Tagged Questions

0
votes
0answers
7 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 ...
1
vote
4answers
153 views

Daily and Differential Backups

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
0answers
36 views

Backup daily for MySQL, MariaDB or PostgreSQL [duplicate]

Good day: I wonder if there is a program with which I can create daily backups with these requirements: The first time, make a full backup. The second time, only the changes since the last backup, ...
0
votes
1answer
20 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 ...
0
votes
0answers
28 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
34 views

PostgreSQL backup error

I am trying to backup my company PostgreSQL database using pgAdmin III, so I selected our company DB from the tree, then right clicked on it and selected 'Backup', selected the destination folder ...
0
votes
1answer
116 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
81 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
59 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 ...
2
votes
1answer
211 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 ...
3
votes
1answer
213 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: ...
2
votes
1answer
97 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
248 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 ...
1
vote
1answer
311 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
646 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
147 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
775 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
288 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
261 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
175 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
96 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
2k 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
718 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
279 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
231 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
855 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
523 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 ...
5
votes
4answers
837 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
735 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
583 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
511 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
75 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 ...