0
votes
1answer
117 views

postgresql - Approach for 'live'/hot file backup?

Forgive me for the ignorant question, but I see postgres has their WAL logs, and there are talks about using filesystem snapshots, and the WAL with the snapshots may or may not be sufficient ...
1
vote
1answer
130 views

One-line backup, compress, scp database file

I support a company with a remotely hosted web site. They use PostgreSQL dbs. I can't write scripts on the remote server, and I don't want to store any backup files on the remote server, but I can ...
2
votes
3answers
456 views

Backup a big PostgreSQL database continually

I have a 20 Tb database in PostgreSQL 9.2 and I have no idea how to efficiently create backups. It's a database that grows a little bit every day and on the 1 of each month it grows about 200 Gb. I ...
2
votes
1answer
101 views

What is “backup mode” in postgresql?

I am in the process of setting up a standby server for a PostgreSQL 9.3 install. I was looking to use pg_basebackup to get the initial starting point for the standby server. However, the documentation ...
0
votes
1answer
91 views

Backing up remote Postgres database

I have a Windows 2008 Server machine with a postgres database running on it. I'd like to back it up daily and have it sent to another machine (running Ubuntu or Windows). What's the best way to set ...
1
vote
1answer
122 views

Extracting PostgreSQL dump in a disk efficient manner

I have a 15 GB compressed PostgreSQL dump (as a pgbackup), that when extracted is closer to 60 GB. I have a cloud server with only 69 GB of free space (54 GB after the pgbackup is copied onto the ...
1
vote
2answers
759 views

What is a typical Compression Ratio for an SQL Database backup file? [closed]

One of my clients want us to give a safe estimate of how much disk space does a compressed SQL backup file take. They need to size their server hard disks accordingly. We can rouhgly estimate the ...
1
vote
2answers
1k views

How can I restore a microsoft sql server database backup to postgres?

I need to take a Microsoft SQL Server database backup (dump) file and import/restore into a PostgreSQL database. Does anybody have some sort of transform script (maybe a perl/sed/awk script) that ...
0
votes
1answer
298 views

Recover Redmine Installation on Ubuntu

I am getting crazy with something and I would like some help if there is someone can help. I had an Ubuntu Server where I was running the Redmine Project Management application; and some days ago the ...
3
votes
1answer
560 views

pg_dump backup compression

When executing a pg_dump which should I use and why? pg_dump -U <user> <database> | gzip -c > backup.gz or pg_dump -F c -f backup.tar.gz -U <user> <database>
1
vote
3answers
354 views

PostgreSQL disaster recovery options

My customer has quite a large (the total "data" folder size is 200G) PostgreSQL database and we are working on a disaster recovery plan. We have identified three different types of disasters so far: ...
1
vote
1answer
179 views

Backup broken PostgreSQL 8.4 without pg_dump

So. I have a problem. PostgreSQL 8.4 won't start or restart without any output given. But it worked for 3 monthes until hosting provider doesn't rebooted server. Now it is completly broken. It wan't ...
0
votes
1answer
829 views

Cron job failing to backing up a Postgres database

I'm unsure what's going on here: I've got a backup script which runs fine under root. It produces a >300kb database dump in the proper directory. When running it as a cron job with exactly the same ...
2
votes
1answer
117 views

Need help recovering PostgreSql database to a new server

As said in the topic I need some help restoring a database to a new server. There is a production database on one server - I do a daily automated backup of that database to a remote location: pgdump, ...
1
vote
1answer
3k views

How to restore a file system level copy of a PostgreSQL database (not dump) to a different PC

I am new to PostgreSQL. I have to recover a database which was running in widows XP machine. I have the zip folder of postgres. I have extracted postgres installation in a different PC and started a ...
0
votes
2answers
143 views

Is it possible to restore table without backup in postgres

I have made some unfortunate UPDATE on a Postgresql table. Is it possible to reverse that change without any backup / rollback save etc?
2
votes
1answer
687 views

pg_basebackup: could not get WAL end position from server

I want to create a Base Backup with the pg_basebackup tool of postgres 9.1. After some seconds I receive: `pg_basebackup: could not get WAL end position from server What can be the reason for this ...
3
votes
2answers
4k views

.pgpass with postgreSQL not working

I have put a .pgpass file in /folder/.pgpass and it looks like this *:*:*:postgres:password_for_postgres I have a bash script where I backup the database from postgreSQL: #!/bin/bash export ...
4
votes
1answer
296 views

What grants are required to run pg_dump?

I'm trying to automate the creation of a user for database backup on Postgresql. What grants are required to run pg_dump?
1
vote
3answers
1k views

Postgresql daily backup

Is there any simple way to backup all your local databases daily (same postgresql server)? I found some bash script that I can use but they seem very complex for such a job. Any easy suggestions ?
2
votes
2answers
2k views

PostgreSQL continuous archiving not running archive_command

I've been trying to set up continuous archiving for a simple, test PostgreSQL 9.0 database, as per the documentation. In postgres.conf I've set: wal_level = archive archive_mode = on ...
1
vote
2answers
519 views

Is there an easy way to partially backup Postgres?

I have a database running on postgres (8.4) and I want regular backups of parts of that data. We have very important data of which we need regular backups in case the hardware catches fire. But the ...
1
vote
1answer
3k views

Postgresql base backup script

I'm using the following script to do a file-level backup of Postgresql. I sometimes see that the last part, to do cleanup after "pgs_backup_stop" is called, hangs while it waits for the last WAL to ...
1
vote
1answer
1k views

PostgreSQL Backup/Restore

What's the best way to backup a postgresql database? I've tried using the documentation at www.postgresql.org, but I always get integrity errors when restoring. Right now I'm using this for backup: ...
1
vote
3answers
493 views

Hot swapping for Linux web/database servers

Is there a way to perform the following under Linux: There are two web servers, main and backup There are two database servers (postgres), main and backup Web Servers are in sync with each other, ...
0
votes
2answers
275 views

pgdump using Windows Taskscheduler

I'm kind of lost on this one. I have a few DBs running on a Postgres instance, on a production machine with Windows Server 2008, and I want to schedule backups to the databases. All I need are ...
2
votes
1answer
192 views

Shadowing MySQL and PostgreSQL to each other

I have a project currently based on PostgreSQL 8.1. However, some of the data processing for it needs to happen on a server for which 7.4 is the latest version (for now, only official packages via yum ...
14
votes
1answer
11k views

How can I do a dump of only the table structure in PostgreSQL?

In a similar vein to this question, how would I do a schema-only dump in PostgreSQL?
13
votes
5answers
10k views

What's the best way to automate backing-up of PostgreSQL databases?

I find it tedious to have to backup databases every week. And I also think weekly backups should be turned into daily backups. If I had to do that, I don't want to do it manually. What's the best way ...
17
votes
9answers
9k views

Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?
2
votes
4answers
2k views

Can't restore PostgreSQL database backup

The backup was created from a database with the UTF-8 encoding using pg_dump. The backup is in the tar format. I then created a new database on another server running the same version of PostgreSQL ...
4
votes
3answers
1k views

PostgreSQL encrypted backups

Is it possible to ensure that dumps taken from a PostgreSQL db are always encrypted? The data in the database is highly sensitive and we cannot afford un-authorized personnel, including Sys Admins ...