2
votes
1answer
39 views

Recovering a partial PostgreSQL archive

I have $PGDATA/base but nothing else. It was taken from an installation after the postgreSQL service was shutdown, so is clean. Is there any way to recreate the other elements in $PGDATA that allow ...
0
votes
1answer
49 views

Postgresql create database from backup programmaticly [closed]

I got a big problems here. Let's say I got the table A (with columns like id, name etc) in the main database. So when I making INSERT into A I need to create new database lets say B_id. I got .backup ...
0
votes
1answer
50 views

What is best strategy for backup on streaming replication(hot standby) environment

I have questions. What is best strategy for backup on streaming replication(hot standby) environment. I’d like to do backup on slave server and restore(PITR). First, I set up streaming ...
1
vote
0answers
58 views

Get postgres snapshot from Amazon RDS

I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with. Right now I'm doing a pg_dump when I ...
0
votes
1answer
52 views

Postgres Point-In-Time Recovery with a limited point in time

I'm configuring PostgreSQL WAL-based backup for point-in-time recovery. Actually, I don't really need any point in time beyond 1 month ago and don't want to waste time for the logs older than that. ...
0
votes
1answer
85 views

Taking hot backup of slave node in postgres (master-slave config with repmgr)

I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave ...
3
votes
1answer
40 views

selective undoing of commited transactions in PostgreSQL

I have inherited a small (<1M rows) PostgreSQL DB which contains complex business rules split across several tables. Updates are very light, reads not so heavy, but the data is extremely valuable. ...
3
votes
2answers
469 views

PostgreSQL: How to backup only One Schema from a database and restore it on another server

I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres. Do I have to create a new ...
1
vote
1answer
232 views

Restoring PostgreSQL 9.1.9 backup with 9.3.0 results in error [closed]

How can I resolve the following errors in 9.3.0: psql mydb < ~/mydb_2013-10-25.backup.tar results in ... ALTER TABLE ALTER TABLE ALTER TABLE ERROR: could not open file "$$PATH$$/2309.dat" for ...
2
votes
1answer
213 views

pg_dump custom format between postgres versions

I was wondering how stable the custom pg_dump format is between Postgres versions. Can I for example restore a custom format dump created with Postgres 8.x in postgres 9.x? Background: I am building ...
0
votes
1answer
1k views

Can't backup a Postgres database: FATAL: Peer authentication failed for user “postgres”

I can connect to my database no problem remotely, either from PHP scripts on our webserver or using PGAdmin3. Unfortunately, when I try to run pg_dump backups locally on the server itself, I get: ...
0
votes
1answer
119 views

pg_dump: SQL command failed

I am trying to take backup of postgresql 9.0 by using pg_dump command. command here is: cd /opt/PostgresPlus/9.0AS/bin and hit the following command pg_dump -h xxx.xxx.xxx.xxx -p 5432 -U superuser ...
2
votes
1answer
336 views

I need help understanding Postgres's archive cleanup functionality

I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform ...
7
votes
2answers
1k views

How to do incremental/differential backup every hour in Postgres 9.1?

Trying to do an hourly hot incremental backup of a single postgres server. I have the following setup in postgresql.conf: max_wal_senders=2 wal_level=archive archive_mode=on archive_command='copy ...
1
vote
1answer
121 views

Should my Postgres backup server be as beefy (IO, processor, RAM) as my master?

I've set up a Postgres database on a server with 2 CPU cores, 4GB of RAM and an SSD volume (on top of RAID 10 of course). This is our (sort-of) "beefy" production database server (I can easily add ...
3
votes
1answer
267 views

Postgres backup and WAL to S3

We are looking for a solution for the following problem: We have set up streaming replication so we have a master DB and a slave DB, we want to have basebackups and WAL files sent to our S3 storage ...
0
votes
4answers
987 views

Daily and Differential Backups [closed]

Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements: The first time, make a full backup. The second time, only the changes since the last ...
0
votes
1answer
97 views

OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql)

I would intuit that it's fine, but I just want to make sure there are no gotchas from a recovery point of view: If I were to lose my temp tablespace upon system crash, would this prevent proper crash ...
-1
votes
1answer
139 views

Should I keep pg_xlog on the same disk as data if using disk snapshotting?

We're running on EBS volumes on EC2. We're interested in leveraging EBS snapshotting for backups. However, does this mean we'd need to ensure our pg_xlog is on the same EBS volume as our data? (I ...
0
votes
1answer
277 views

Postgresql: Difference between pg_dump and backup/restore?

Don't they essentially do the same thing by storing all the data in a file and dumping it into another database? Is it that pg_dump just adds the data on to whatever is already there and restore ...
3
votes
2answers
222 views

How do I solve an error code 127 when doing a pg_dumpall through a cronjob?

I have this simple script to backup all my databases in postgres pg_dumpall -U pg_user > /tmp/tmp.txt DUMP_STATUS=$? echo "Dump status: $DUMP_STATUS" > /tmp/status.txt The dump ...
4
votes
1answer
95 views

Postgresql, write archive file over mount, secure against simultaneous access?

I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share. I just use the basic command, given as an example in the postgresql.conf test ! -f /mnt/server/archivedir/%f && cp ...
3
votes
2answers
894 views

Running pg_dump on a hot standby server?

Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask. I would like to run a nightly backup job (via pg_dumpall) from a hot ...
5
votes
1answer
987 views

Backup a database with a huge number of tables

Is there a way to backup a PostgreSQL database with a huge number of tables? An attempt with pg_dump for a database of about 28000 tables resulted in the following error message: pg_dump: WARNING: ...
3
votes
1answer
201 views

Postgres Backup queries on AWS after reading Postgres docs

I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance. So there are 2 ways to do it. Create a dump using pg_dump. File level backup with ...
1
vote
1answer
382 views

PostgreSQL - How can I manage WAL archiving for different databases on a server?

I'd like to use WAL archiving for incremental backup of my database. But I have 2 databases on my server, and I can't afford the disc space for backup of both of them. So I only want to backup one of ...
2
votes
1answer
617 views

Postgresql WAL Archiving, How to do it properly?

Basically I have one (for now) mission critical postgresql database that I will need up to the minute recovery for, I've never implemented a working WAL archival before so I would like to know what is ...
2
votes
3answers
2k views

Copy PostgreSQL data from one PC to another

I am migrating my server application from the existing system to another system. Unfortunately, the existing system is also the database server. It has data stored. Is it possible to copy this data to ...
1
vote
1answer
217 views

Questions about PostgreSQL backup/restores

I've inherited a large PostgreSQL 8.4.7 database that is currently not being backup at all. The database itself I believe is around 100GB, I've been reading about the different methods to backup this ...
3
votes
1answer
2k views

Why is my postmaster process (sometimes) becoming unmanageable after a WAL base restore?

TL;DR: Un-stoppable, unusable postmaster is being spawned when Postgres is started right after restoring its data directory from a WAL base backup. Why? Context: We run postgresql 8.4, on CentOS 6, ...
6
votes
2answers
429 views

PostgreSQL PITR restore

I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving. On this server, there are three databases: databaseA, databaseB and databaseC. Someone deletes a ...
0
votes
2answers
422 views

Creating a test database from Production database (different machines & servers)

I'd like to set up a test database on a separate server. Production database is MS SQL Server and the test server is a *nix box so I'd like to have the test server running either PostgreSQL or MySQL. ...
2
votes
1answer
216 views

Initial recovery Postgres with backup in sync commit mode

I've got a master and a slave postgres 9.1.3 database. I would like to init the backup database for the first time. I did the following steps: Stopped master Copied all files to Slave Create ...
3
votes
1answer
148 views

PostgreSQL synchronized commit performance

I'm setting up a pair of PostgreSQL servers. Because cash is short I can only afford one high class server and some crapy backup box. If the expensive high end box should catch fire, operations can ...
0
votes
2answers
4k views

Opening a .backup file for PostgreSQL

I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise. In short, I have what I think is a ...
3
votes
1answer
1k views

PostgreSQL Scheduled Database Backup

For scheduling a daily back up on a postgres database we execute the following command: 0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \ > ...
6
votes
1answer
408 views

PostgreSQL Complete Database Backup

I've been trying to figure out how I can completely backup my PostgreSQL database. I have not seen any resource that explains how to do a complete backup. I am aware of the pg_dump and pg_dumpall ...
2
votes
1answer
350 views

Postgresql revert back database value

I have postgresql database. My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table. When I get the data file using following commands. select * from ...
1
vote
2answers
1k views

Backup of Postgres database

I want to create a backup of my database in Postgres. I'm using pgAdmin. on my database I have clicked right button of mouse after that I've selected the "Create a backup of database" After that ...
2
votes
1answer
863 views

Does my choice of pg_dump format impact restore speed?

According to the docs for my version of Postgres (8.4.2), pg_dump can "output in script or archive file formats." The script-formatted dump (4GB) takes 1.5 hours to restore, and I'm wondering if the ...
6
votes
4answers
1k views

Determine when a PostgreSQL database was last changed

I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed? Instead of using pg_dumpall, I'd ...
10
votes
1answer
1k views

How does PostgreSQL handles Checkpoints in the middle of a WAL-enabled backup?

On a PostgreSQL v9.0 I've a WAL archiving system working. So WAL files a regulary archived (when 3 WAL are created or if a WAL is older than 15 minutes). Now I add a binary packup of the PG_DATA ...
3
votes
2answers
864 views

Removing Archive Logs after PostgreSQL PITR online-recovery

I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2. Replication mode is "on" Load balance mode is "on" (between primary and standby1) I followed the official ...
5
votes
2answers
555 views

Save postgres database from broken ubuntu 10.04 server

Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve). I can still access the filesystem. Is there a chance to transfer the database by only ...
4
votes
1answer
80 views

Can I activate PITR after the DB has been used

My Postgres-Server is running for quite a while and contains probably 4-5 GB of data. Backups are done using dumps, and this works ok. Though not most efficient, it just works. Due to future growth ...