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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
dependency tree of tables in PostgreSQL 4 1,103
Good documentation 1 179
Post postgresql  public 4 272
Whats the correct entry into pg_hba.conf  file 12 104
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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

815 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

8 Experts available now in Live!

Get 1:1 Help Now