Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
PostgeSQL Backup

PostgeSQL Backup

---------------------------------------------------------------------
[root@localhost backup]# cat pgsqlbk.sh
#!/bin/bash

DBUSER="postgres";
BAKDIR="/backup/`hostname -s`/psql";
TODAYS_BAKDIR="$BAKDIR/`date +%Y%m%d`";
DATABASES=`psql -U $DBUSER -t -c 'select datname from pg_database'`;

[ ! -d $TODAYS_BAKDIR ] && mkdir -p $TODAYS_BAKDIR;

for DB in $DATABASES; do
ツツツ pg_dump -U $DBUSER $DB | gzip > $TODAYS_BAKDIR/$DB.dump.sql.gz;
done;

find ${BAKDIR} -type d -mtime +7 -print0 | xargs -0 rm -rf;
----------------------------------------------------------------------
Error DB template0
-------------------
root:/var/backup # ./pgsqlbkp.sh
pg_dump: [archiver (db)] connection to database "template0" failed: FATAL:ツ database "template0" is not currently accepting connections
root:/var/backup # su - postgres
postgres@cib:~> psql -U postgres template1
psql (9.4.4)
Type "help" for help.

template1=# select * from pg_database;
template1=# UPDATE pg_database SET datallowconn = TRUE
template1-# WHERE datname = 'template0';
UPDATE 1
template1=#
----------------------------------------------------------------------
drwxr-xr-x 2 root root 4096 Augツ 1 18:06 20160801
[root@localhost ~]# ll /backup/localhost/psql/20160801
total 32
-rw-r--r-- 1 root rootツツ 532 Augツ 1 18:06 pmpdb.dump.sql.gz
-rw-r--r-- 1 root root 14573 Augツ 1 18:06 ponectdb.dump.sql.gz
-rw-r--r-- 1 root rootツツ 409 Augツ 1 18:06 postgres.dump.sql.gz
-rw-r--r-- 1 root rootツツ 407 Augツ 1 18:06 template0.dump.sql.gz
-rw-r--r-- 1 root rootツツ 412 Augツ 1 18:06 template1.dump.sql.gz
---------------------------------------------------------------------

Add comment


Articles by Date

Category Database

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email