Re: Restoring a database from a file system snapshot

William Garrison wrote:

> 1) I have a file system backup that *IS* consistent. So I should not
> need any WAL files at all right?

It is consistent only if it was taken when the postmaster was down.

> **update**
> I got it working. Here's how
> 1) I have a file system snapshot. No WAL files were required.
> 2) Using my example from my first post, here is how I manually
> rearranged the files so that postgres saw the new database.
> - Create a new tablesspace on the new server
> - Create a new database on the new server, using that tablespace. I
> placed it into X:\NewDatabase\
> - PostgreSQL will create a folder X:\NewDatabase\98765. Notice that the
> number postgres created is NOT the same as your old one.
> - Stop PostgreSQL
> - Move your files from Z:\MyDatabase\1234567 into the X:\NewDatabase folder
> - Delete the 98765 directory.
> - Start PostgreSql

This does not really work, because you're missing the necessay pg_clog
files. You can make it sort-of-work by doing a VACUUM FREEZE and a
CHECKPOINT on the database before taking the snapshot. However, you'd
still be missing the entries in shared catalogs. The only one you've
recreated is the one on pg_database, but there are more.

> --- The referenced article is lacking an explanation for how postgres
> arranges the files and how the symlinks for the tablespaces are made.
> Without that knowledge, attaching to another database is guesswork.

Attaching to another database is not supported at all.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support