Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
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.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

604 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