I have a 30 gb db in PostgreSQL 9.2. Running On Amazon Linux on an ec2 (web virtual machine), with the data directory mounted from its own drive (EBS).

I'll call the database db30.

On production it's the only user db.

In qa/dev we get the call for multiple versions.


  • can I get a copy of db30 from production restored as db30_dev3 on the qa? Maybe the last full backup is good, sometimes they need a copy from right now.
  • can you give me a copy of db30_qa2 and call it db30_qajeff? If I hose the data can we recover from the image you restored, not a new copy of db30_qa2?

I've been a mssql dba on physical servers for nearly a decade so I'm totally thinking with a SQL Server mindset.

Right now, I'll pg_dump to a gzip file. 7gb. Might take a while. To restore I would create the new db name, the psql run the SQL file against it, which promptly takes a few hours to apply all of the SQL and build indexes.

In aws and on sans, you have the option of a disk snapshot, which I'm assuming should be like copying the files and attaching them to the server. But the docs say you must grab both Db files and WAL in the snapshot.

Because the WAL is an object of the postmaster, all db's share it. This snapshot method seems only valid for backing up and resorting the whole cluster (what I'd call a running instance of PostgreSQL). It doesn't seem possible to directly snap and restore only a single Db.

So my rookie psql notion would be:

  1. Only one user Db per cluster
  2. Snapshot the whole data directory
  3. Copy snapshot data to another drive and mount as another data directory, where I can bring it online as another cluster.
  4. Details for configuring user and settings files and maybe renaming the Db. Renaming May not be needed on its own private cluster.

So the qa box would have 10 clusters of 1db rather than 1 cluster with 10 DBS. I'm sure that's hell for sharing resources, but it's qa, so that's not the priority.

Is this a good strategy? Is there a better strategy that an experienced psql dba would go to instead?

  • 1
    That's a reasonable approach. It can be frustrating that WAL is shared across all DBs - it's great for efficiency of disk flushes, but a right pain if you want to use WAL shipping and archival. Unfortunately making it per-database is far from simple because of the need to manage transaction IDs, global state like users/databases and so on. So yes, for this particular purpose I'd use one cluster per DB. Out of interest, how would you tackle this on MS SQL? – Craig Ringer Jul 15 '14 at 16:31
  • Yeah I'm working from a more diverse toolbox with mssql (years of experience, not speaking of better/worse features). – ShawnMTherrien Jul 15 '14 at 16:37
  • I'm just curious. I haven't done much with MS-SQL and find that users and devs of one DB can learn a lot from experienced users of another. – Craig Ringer Jul 15 '14 at 16:38
  • Sorry, I was typing a response and accidently posted it and took too long so I couldn't edit it :P In a similar cloud environment, but using MSSQL I would have a single server instance (cluster) and effectively keep each db on it's own drive, then I could just snapshot the drive and reattach a copy of the files. This is a nice feature in AWS and most SANs. Then you have to attach the db files, bring the db online and link the server login security to the "new" db. – ShawnMTherrien Jul 15 '14 at 16:54
  • 1
    Having multiple TL's (WALs) can be a benefit in a few ways. For one, you can configure each to it's own disk to reduce IO contention. On the flip side of the coin having multiple TL's on the same drive will lead to increased IO contention. Multiple logs will lead to non-sequential io while a single log would be sequential. If you don't have your log space preallocated, then when they start growing they'll also fragment the drive greatly. But you can also just pick up one single db/log and move it to another server. – ShawnMTherrien Jul 15 '14 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.