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:
- Only one user Db per cluster
- Snapshot the whole data directory
- Copy snapshot data to another drive and mount as another data directory, where I can bring it online as another cluster.
- 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?