pg_backup_copy.sh (Источник) 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
#!/bin/bash
DB_LIST=(`sudo -i -u postgres psql -l | awk {'print $1'}`)
MASTER_USER=
MASTER_PW=
INSTANCE=""
PORT=5432

mkdir -p /tmp/dump

sudo -i -u postgres pg_dumpall --roles-only > /tmp/dump/roles.sql
sed -i '/postgres/d;/CREATE ROLE/d;/WITH SUPERUSER/d' /tmp/dump/roles.sql
cp /tmp/dump/roles.sql /tmp/dump/roles2.sql
sed -i 's/ALTER/CREATE/' /tmp/dump/roles.sql

for tbl in $(cat /tmp/dump/roles2.sql | grep "ALTER" | awk {'print $3'} | grep user)
do
    echo "GRANT $tbl TO $MASTER_USER;" >> /tmp/dump/grant.sql
done

echo "Restore roles..."
PGPASSWORD=$MASTER_PW psql -q -h $INSTANCE -U $MASTER_USER postgres -f /tmp/dump/roles.sql

echo "Set grant..."
PGPASSWORD=$MASTER_PW psql -q -h $INSTANCE -U $MASTER_USER postgres -f /tmp/dump/grant.sql

for db in "${DB_LIST[@]}"
do
        sudo -i -u postgres pg_dump -d ${db} --create --no-owner > /tmp/dump/${db}.sql
        sed -i "s/postgres/$MASTER_USER/" /tmp/dump/${db}.sql
        PGPASSWORD=$MASTER_PW  psql -q -h $INSTANCE -U $MASTER_USER postgres -f /tmp/dump/${db}.sql
done
rm -r /tmp/dump