Skip site navigation (1) Skip section navigation (2)

Re: Restoring a database from a file system snapshot

From: William Garrison <postgres(at)mobydisk(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Postgres General List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Restoring a database from a file system snapshot
Date: 2008-08-27 20:32:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Alvaro Herrera wrote:
> William Garrison wrote:
>> I have a PostgreSQL database on Windows Server 2003, and the database is  
>> kept on a SAN that has the ability to make instantaneous snapshots.   
>> Once I have made such a snapshot, I am unclear how to re-attach it to  
>> another postgres database on another machine.  Postgres seems to create  
>> a directory structure that looks like this:
>>    Z:\MyDatabase
>>    Z:\MyDatabase\PG_VERSION
>>    Z:\MyDatabase\1234567
>>    Z:\MyDatabase\lots of files...
>> The "1234567" number above changes with each new database I create.
> It doesn't work.  There's a procedure for restoring files, but you need
> to also save the pg_xlog files as a stream, for which you need to set up
> an archive_command in postgresql.conf beforehand.  If you're interested
> in this, see the "Point in time recovery" chapter in the documentation.
> pg_dump/pg_restore is the easiest combination to use anyway.
I've read (and I am reading it again now) that chapter, and I'm making 
this post because that documentation doesn't seem to address the 
scenario I am in.  The PITR article 
shows you how to use the WAL archives after a file system backup that is 
not consistent.  But it doesn't address two vital things:
1) I have a file system backup that *IS* consistent.  So I should not 
need any WAL files at all right?
2) It doesn't explain exactly what folders you should be creating a 
backup of, or how exactly to restore those files onto another system, or 
how to munge the tablespaces to work.  Specifically, it says
    "Restore the database files from your backup dump... If you are 
using tablespaces, you should verify that the symbolic links in 
pg_tblspc/ were correctly restored." 
    That's exactly what I'm asking how to do.

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

What I was doing before was moving the files from Z:\MyDatabase\1234567 
into X:\NewDatabase\98765.  The log file would then log a message like
2008-08-27 13:24:23 FATAL:  database "Your Database Name" does not exist
2008-08-27 13:24:23 DETAIL:  The database subdirectory 
"pg_tblspc/32789/12345" is missing.
It specifically wants the old folder name, not the new folder name of 
the new database you are attaching into.

--- 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.  It 
really only took me a few tries to get the folder correct, but I think 
there needs to be an attach procedure for attaching an existing 
database, or the article should describe the folder structure used by 
postgres.  It isn't as simple as just copying the folder.  You have to 
also rename the directory structure to match.  That's what I needed to 
know.  meh, it was easy to do, just unclear.

In response to


pgsql-general by date

Next:From: Tino WildenhainDate: 2008-08-27 20:42:18
Subject: Re: Dumping/Restoring with constraints?
Previous:From: Markova, NinaDate: 2008-08-27 19:46:55
Subject: Postgres in a solaris zone

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group