Postgresql Backup Question(Using WinSCP)

Posted on 2006-11-16
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 10

Accepted Solution

ivanovn earned 1000 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 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.

Assisted Solution

fifo_shmifo earned 1000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

762 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