Generally, I like to work with MySQL but some time my work force me to work with PostgreSQL database server.
Recently I had received a request to backup PostgreSQL databases as one of our client want to format and reinstall RHEL server.
PostgreSQL is a one of the robust, open source database server. Like MySQL database server, it provides utilities for creating a backup.
Step # 1: Login as a pgsql user
Type the following command:
$ su - pgsql
Get list of database(s) to backup:
$ psql -l
Step # 2: Make a backup using pg_dump
Backup database using pg_dump command. pg_dump is a utility for backing up a PostgreSQL database. It dumps only one database at a time. General syntax:
pg_dump databasename > outputfile
Task: dump a payroll database
Type the following command
$ pg_dump payroll > payroll.dump.outTo restore a payroll database:
$ psql -d payroll -f payroll.dump.outOR$ createdb payrollHowever, in real life you need to compress database:
$ psql payroll $ pg_dump payroll | gzip -c > payroll.dump.out.gzTo restore database use the following command:$ gunzip payroll.dump.out.gzHere is a shell script for same task:
$ psql -d payroll -f payroll.dump.out
#!/bin/bash DIR=/backup/psql [ ! $DIR ] && mkdir -p $DIR || : LIST=$(psql -l | awk '{ print $1}' | grep -vE '^-|^List|^Name|template[0|1]') for d in $LIST do pg_dump $d | gzip -c > $DIR/$d.out.gz done
Another option is use to pg_dumpall command. As a name suggest it dumps (backs up) each database, and preserves cluster-wide data such as users and groups. You can use it as follows:$ pg_dumpall > all.dbs.outOR$ pg_dumpall | gzip -c > all.dbs.out.gzTo restore backup use the following command:
$ psql -f all.dbs.out postgresReferences:
🐧 29 comments so far... add one ↓
HowTo: Backup MySQL Databases, Web server Files to a…
SSH: Rotate backup shell script to remove…
Howto Setup PostgreSQL in the OpenSolaris OS
Use amanda to backup your Linux server - Howto- Linux: Iptables Allow PostgreSQL server incoming request
Comparison PostgreSQL vs MySQL database server
How Do I Enable remote access to PostgreSQL database server?
Download of the day: PostgreSQL 8.3 Database Server
| Category | List of Unix and Linux commands |
|---|---|
| Disk space analyzers | ncdu • pydf |
| File Management | cat |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | NetHogs • dig • host • ip • nmap |
| OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
| Package Manager | apk • apt |
| Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
| Searching | grep • whereis • which |
| User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
| WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
sir,
i am not able to take the backup of postgresql database.in pg_dump commnad it provide me “access denied ”
thanks
can you provide how to do offiline backup of the postgresql database.
Man, please remember that if you use the gzip option with pg_dumpall you HAVE TO use the gunzip to restore the backup.
gunzip all.dbs.out.gz
and then
psql -f all.dbs.out postgres
The script has a little bug. On,
LIST=$(psql -l | awk ‘{ print $1}’ | grep -vE ‘^-|^List|^Name|template[0|1]’)
if you have a list with several databases, the last row is number. For example in my case:
(19 rows)
and after the script
(19
so I propose you generate the LIST with this:
LIST=$(psql -l | awk ‘{ print $1}’ | grep -vE ‘^-|^List|^Name|template[0|1]|^(‘)
Otherwise, it’s a very nice article.
Thanks.
Thanks Vivek for the great script.
Just a few notes:
In the 3rd line
[ ! $DIR ] && mkdir -p $DIR || :
there should be no space character after the exclamation mark!
[ !$DIR ] && mkdir -p $DIR || :
like this it works for me.
@Leopold, thanks for your comment, I had the same problem and your expression for LIST was the solution 🙂
i have taken backup of DB from other machine. so i want the run those DB on my box.
what are the steps i need to follow to achive it.
please help me
Needed help,new person in software using POSTGRE SQL please guide me with commands that will help to take backup easily and similarly restore when needed.
Nice tip, thanks. I have a few notes to share.
You can also avoid the number of rows returned with the “-t” option to psql:
psql -lt
Also, to run the script as root from cron, I used su to run the backup as the postgres user. My complete script:
#!/bin/sh DIR=/backup/pgsql LIST=$(su - postgres -c "psql -lt" |awk '{ print $1}' |grep -vE '^-|^List|^Name|template[0|1]') for d in $LIST do su - postgres -c "/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz" doneHi
I want take backup from Postgresql in Tehran (city in Iran) and store it in Sqlserver 2005 in Mashad (city in Iran). Is it possible? How can I do it?
Regards,
Hamidreza
Hi. I had a slightly different experience. My cron job runs under root, but wanted to run the psql code under postgres user. My script therefore had to be changed to (Note the PGPASSWORD being set AND removed at the end):
#!/bin/bash
export PGPASSWORD=XXXXX
DIR=/home/ben/backups/psql
[ !$DIR ] && mkdir -p $DIR || :
LIST=$(su postgres -c “psql -lt” |awk ‘{ print $1}’ |grep -vE ‘^-|^List|^Name|template[0|1]’)
for d in $LIST
do
pg_dump -U postgres $d | gzip -c > $DIR/$d.out.gz
done
export PGPASSWORD=
Hi Denial this script is great for me -Brahma Prakash
Nice,
For my case, I add split to have many files and can store it in dumy filesystem
pg_dumpall -Upostgres | gzip -c | split --bytes=1000mRegards
I got errors like this:
-su: /tmp/pgsql/erpengdemo.sql.gz: Permission denied
and tish:
pg_dump: [archiver (db)] connection to database “:” failed: FATAL: database “:” does not exist
And work around was:
#!/bin/sh
DIR=/backup/pgsql
[ !$DIR ] && mkdir -p $DIR || :
chown postgres:postgres $DIR
LIST=$(su – postgres -c “psql -lt” |awk ‘{ print $1}’ |grep -vE ‘^-|:|^List|^Name|template[0|1]’)
for d in $LIST
do
su – postgres -c “/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz”
done
I got errors like this:
-su: /tmp/pgsql/erpengdemo.sql.gz: Permission denied
and tish:
pg_dump: [archiver (db)] connection to database “:” failed: FATAL: database “:” does not exist
And work around was:
#!/bin/sh
DIR=/backup/pgsql
[ !$DIR ] && mkdir -p $DIR || :
chown postgres:postgres $DIR
LIST=$(su - postgres -c "psql -lt" |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]')
for d in $LIST
do
su - postgres -c "/usr/bin/pg_dump $d | gzip -c > $DIR/$d.sql.gz"
done
My version of script. The differencies:
1. I am asking psql to output databases without header (-t)
2. Simplified awk call to get rid of colons
#!/bin/bash DIR=/backup/psql LIST=$(psql -l -t -U root | awk 'NF >=5 { print $1}') for d in $LIST; do pg_dump $d | gzip -c > $DIR/$d.out.gz doneEven better version 🙂
#!/bin/bash # # Dayly PostgreSQL maintainance: vacuuming and backuping. # ## set -e for DB in $(psql -l -t -U root | awk 'NF >=5 { print $1}' | grep -v template0); do echo "[`date`] Maintaing $DB" echo 'VACUUM' | psql -U root -d $DB DUMP="$DB.`date '+%Y%m%d'`.sql.gz" PREV="$DB.`date -d'1 day ago' '+%Y%m%d'`.sql.gz" pg_dump -U root $DB | gzip -c > $DUMP md5sum -b $DUMP > $DUMP.md5 if [ -f $PREV.md5 ] && diff $PREV.md5 $DUMP.md5; then rm $DUMP $DUMP.md5 fi doneI’ve made some script good for cron… It also has impemented restore commands for created backups 🙂 and expiration limit (rotating)
#!/bin/bash # # Little script for PGSQLdump all databases (seperate files) # # v20100519 -- initial version by Koco # ############################################################ MYEXECNAME=backup_postgres_v8.4_dbs_script.bash USER=postgres PGSQLCMD="/postgres1/v8.4.0/bin/psql --port 5433" PGSQLCMDlist="$PGSQLCMD -l -t" PGSQLdumpCMD="/postgres1/v8.4.0/bin/pg_dump --port 5433 -C" APPEND=`date "+%Y%m%d-%H%M"` BASEDIR=/postgres1/BACKUPS BKPDIR=$BASEDIR/bkpV8.4.$APPEND RSTFILE=$BASEDIR/bkpV8.4.$APPEND/1_restore_commands.README #How many days do you wanna keep backups TRESHOLD=30 if [ -f /postgres1/.postvolume ] then DATABASES=`su - $USER -c "$PGSQLCMDlist" |grep | |awk '{ print $1}'| grep -vE '^-|^List|^Name|template[0|1]'` mkdir $BKPDIR for db in $DATABASES do su - $USER -c "$PGSQLdumpCMD $db" | bzip2 -c > $BKPDIR/$db.dump.bz2 echo "#---restore $db" >> $RSTFILE echo "bzcat $BKPDIR/$db.dump.bz2 > $BASEDIR/RST.dump.sql" >> $RSTFILE echo "su - $USER -c "$PGSQLCMD -d $db -f $BASEDIR/RST.dump.sql" " >> $RSTFILE echo "rm $BASEDIR/RST.dump.sql" >> $RSTFILE echo "#" >> $RSTFILE done fi # And find those old ones... and delete them (use nwrecover if you need them back) FILES=`find $BASEDIR -type d -name 'bkpV8.4.*' -mtime +$TRESHOLD` for f in $FILES do echo "$APPEND: Treshold reached. Removed $f backup." >> $BASEDIR/$MYEXECNAME.log rm -rf $f doneHi,
I’m having problems with script and commands. could you help me with it?
#!/bin/bash
DIR=”/var/lib/pgsql”
BASE=”coupons”
DATE=”date +%m%d”
pg_dump $BASE | gzip > $DATE.coupons.gz
exit
Hello,
We were using the PostgreSQL for Bugzill, Now we lost the Password Doc, how can we take the backup of the database,
We have the root password
regards
You can list dbs with:
A great example can be found here: https://www.rtfm.ro/baze-de-date/postgresql/backup-automat-la-bazele-de-date-postgresql/
where should i type that commands?
In your terminal (shell)
Hi everyone,
I am new to freebsd, and I used postgresql for my database management.
There was this scenario that i accidentally delete this particular database
and the backup setting for our server is not one by one, meaning all databases.
My question is how can I restore that particular database using the backup file which is a compressed file which is composed of several database?
Is there a way to extract a particular database on that file?
Thanks Vivek for ur great help 🙂
Another version to get the list of databases:
psql -lqt | grep -vE ‘^ +(template[0-9]+|postgres)? *|’ | cut -d’|’ -f1| sed -e ‘s/ //g’ -e ‘/^$/d’
As my `psql -lqt` output is:
abcdefghij | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
abc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
I taken dump using pg_dump > sample.sql
where the file is goin to save?
In your current directory. When you type the command from directory “/home/michael”, the backup will be in this dir.
Thank you so much! 😀
HI,
> Does RHEL supports auto backup and restoration for PostgreSQL? If yes how to configure.
> Does Ubuntu supports auto backup and restoration for PostgreSQL? If yes how to configure.
Thanks
Tilak