- App Store
PostgreSQL: Backing up your data
Backups can be one of the most critical aspects of administering any database, and PostgreSQL is no exception. While the PostgreSQL database is very versatile and resilient, problems can happen. A power failure could occur at worst moment corrupting the database or the hard-drive could fail. You could also have problems with users, authorized or unauthorized, changing or destroying data, we hope by accident, but sometimes malicious people can do bad things when presented the opportunity.
The list really could be limitless, but if you're following ERP Best Practices, then you know the best way to protect yourself is to make regular backups of your database. The depth of the situation you want to safeguard against can dictate your requirements for how often, when, where you store your backups. The first thing is make regular backups. The second is to store them in a safe place. If you are storing your backups on the same server you run your database on it's possible you could lose your database and your backups if the drive fails.
At xTuple we provide a backup service as part of our XTN support service. Users of this service have their database backed up nightly and the file is transmitted to our servers giving you an off-site backup. We also keep a few days of backups in case one is bad and we have to go back in time. In addition each week we burn one of the backups to a CD for longer storage. I'm not going to delve into all the details here but would encourage you to ask about that service and seriously look into it.
You can easily do your own backups. You don't need our XTN backup service to have good backups, and I would encourage anyone to have their own backups even if they have our XTN backup service. You can never have enough backups when you need them.
Using the tools provided by the PostgreSQL database you can backup and restore database very easily. There are two different types of backups you can use: sql text backups and binary backups. Binary backups are convenient and let you do a couple different things when you load them back up however they are limited to backing up a single database and do not include users and passwords. The sql text backups allow you to backup your entire server including user and passwords but you cannot restore a single database or just the users unless you edit the file. Both of these files are created using the same pg_dump command depending on the options you specify. Both are restored using different commands. Another thing to consider is that binary backups can be restored using pgAdmin.
I use Linux command line tools, but these tools are available on both Mac and Windows as well. The pg_dump commands can be done with a cron entry on Linux and Mac while Windows will have to use some other method to run the command line tools on a regular basis. Here is an example of backing up a entire database including the users and restoring it to another clean server:
pg_dumpall -U postgres -h old.server.com -p 5432 -f mybackup.sql
psql -U postgres -h new.server.com -p 5432 -f mybackup.sql template1
In the above example the -U postgres says connect as the postgres user. The -h some.server.com is the host. The -p 5432 is the port. Finally the -f mybackup.sql is the name of the backup you want to write to or read from. In the restore we added template1 to the end. That is because psql needs a database name to connect to regardless of the fact that the restore will create and connect to other databases so we gave it the command template1 database which should be in all PostgreSQL database servers.
If you want to backup just a single database to a binary file and restore it to a different database name you use the following:
pg_dump -U postgres -h old.server.com -p 5432 -F c -f mybackup.backup dbname
createdb -U postgres -h new.server.com -p 5432 newdbname
pg_restore -U postgres -h new.server.com -p 5432 -d newdbname mybackup.backup
You'll notice a lot of the same options we used previously. Most notably the backup file we named .backup to distinguish between the sql text backups. We also added the option -F c to the pg_dump command which tells it we want a compressed binary backup. Finally we had to create the new database before we would restore into it.
These really do just scratch the surface. The biggest message I would like to get across is the importance of doing backups. Hopefully this has gotten you thinking about backups and how easy they could be to do. The PostgreSQL documentation has a great deal more information about the command line tools I have referenced. You can also use pgAdmin to do all of these tasks as well.
Thu, 09/17/2009 - 10:27#1
Cron like scheduling on Windows
You can use the Task Scheduler on windows to run jobs periodically by either navigating through the UI (Control Panel -> Administrative Tools -> Task Scheduler) or using the command line version schtasks.
Sat, 09/26/2009 - 10:10#2
PostgreSQL Transaction logging
Taking and storing backups is a crucial process for recovering from problems. If the system crashes or data is destroyed or altered by users (authorized or unauthorized) then even with a daily backup you will still lose, on average, a half day of work.
Does PostgreSQL support transaction logging? If you have a daily backup and all transactions logged then you can (1) restore to the previously known good end-of-day state and then re-apply the known good transactions to restore the system to a point between backups.
Sun, 09/27/2009 - 18:58#3
I believe it does
This portion of the postgres docs goes into a lot of detail on that subject.
Tue, 11/27/2012 - 22:25#4
backing up using cron on linux
I would like to schedule automatic backups using cron on an Ubuntu linux system. The problem I see is that a password is required. How could I do a cron backup and still enter a postgres password in a secure manner? I would avoid putting a password in a script that could be seen by unwanted eyeballs. I guess one could set permissions on the script so only authorized users/groups could view it.
Is there a better way? I would also be interested in any bash scripts used for cron backups.
Wed, 11/28/2012 - 01:38#5
linux & cron - solved
I posted too soon. I solved this with the help of some other example scripts on here. .pgpass solves the password problem. Piecing together other examples I came up with this below. It uses pg_dump and the rsync to copy to another computer named backup_server. It embeds the year-month-day-hour-minute in the archive file name, so a backup made today is named:
# backup quickstart db and copy to a backup server
if bin/pg_dump quickstart | gzip > $tname
if rsync -av $tname backup_server:/root/$fname
echo $0": pg_dump and rsync to backup_server complete"
echo $0": ERROR backing up xtuple failed!"