Postgresql Backup Question(Using WinSCP)

Posted on 2006-11-16
Last Modified: 2008-02-01
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?


Question by:buckstaff
LVL 10

Accepted Solution

ivanovn earned 250 total points
ID: 17959241
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 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.

Assisted Solution

fifo_shmifo earned 250 total points
ID: 18402834
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  -->

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: ( This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now