• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1412
  • Last Modified:

Postgresql Backup Question(Using WinSCP)

I currently backup our postgres database using pg_dump, which dumps it to a .sql file.  I was wondering if I can simply copy the entire data folder which resides in /var/lib/postgres

If I copy that entire data folder using WinSCP, which also contains these folders(base, pg_xlog, pg_clog, and global) will this give me a complete backup of our database?  The database resides on our Debian server, and I would be copying this folder to our Windows Domain server so I can back it up to tape.  

Is this the best way to back things up?


2 Solutions
You could use PITR with WAL to backup your database using the filesystem copy and then use the logs if you wish to do incremental backups.

Check http://www.postgresql.org/docs/8.1/interactive/backup-online.html for full details of the procedure.

The general idea is to enable log archiving, take a base backup (that can then be copied via WinSCP to wherever you want it), and then use the archived logs (which you can also backup using WinSCP) to recover the database later.
That is not a very good way of backing up PostgreSQL. Some DBs support hot backups, but not our friend PG. The reason being that PostgreSQL might be busy writing some data when you suddenly grab an incomplete file. Next time you try and restore the database, PG will simply refuse to start.

This doesn't always happen though, I have had times when a hot backup worked, and times when it didn't...

Some options for PG backups:
1. If you wish to back it up like that you would need to stop the service, copy the directory and start the service again.
2. Another option would be to use a simple PHP script and use it to do the backups (see below). For this option you need to have Apache/PHP installed.
3. Use specialized PostgreSQL backup utilities.
4. Use the PostgreSQL Write Ahead Logs to create backups. This is pretty complex and not always a solution people choose for ease. The setup thereof, and the backup restoration is a pretty complex story. This option is recommended for larger databases though.

Personally I use backup scripts as described next.

--- Backup Script ---
This script is a simple script that would stream an SQL dump to you when you access it through your web browser.
For this you would have to have apache/php installed on the web server. Let say you save the script into the web document
root under http://server_host/pgbackup.php, when you access the script it will popup a "Save File" dialog which would allow you to simply save the dump. This is a bit more automated than the current solution you are using.

If you wish to have the copy done securely, setup Apache to use SSL and access the script with https:// instead of http://

header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment;\n filename=\"dbdump-".date("Y-m-d").".sql\"");
system("pg_dumpall -U postgres -c");
<!-- END COPY HERE  -->
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now