Restoring data from TABLESPACE files

[Temp key: basic, via spamcop]

Context:
OSX LIon Server 10.7.5; system supplied PostgreSQL 9.0.5;
user data on dedicated drive via TABLESPACE,
i.e., keeping my stuff away from the OS data tables
Backup via TimeMachine on hourly basis (i.e., file system backup)
TODO: included determining a "better way" of backing up but
delayed/inhibited by concerns that restoring pg_dump files would
have to restore the OS defined tables which did not seem to be the
correct thing to do.

Scenario:
Dedicated drive (Mac Mini Server 2x 500G: internal drive) failed,
and computer returned to Apple for warranty repair.
Another computer was reconfigured by restoring from backups, and all files returned,
Except I had trouble getting a suitable drive for the TABLESPACE data (rural location).
Once the TABLESPACE files were restored and the database
stopped/started etc. they were not mounted/recognized by postgres.

Checked:
entry in postres.db_tablespace.splocation column is correct (note with approval 9.2 change)
symlink is correct, i.e., same as above
header folder: PG_9.0_201008051 present and populated
Permissions correct and all files owned by _postgres:_postgres
PgAdmin3 reports the TABLESPACE as being present, but none of its defined roles or tables

Questions:
1. What other entries do I need to check/adjust to get the data files mounted?
2. Is there a tool that can read the system files and reconstruct the data they contain?
3. Which commercial services offer data recovery in this context?

So far I have:
- Taken on board all the on-line advice about a better backup system,
thank you in advance if you feel the need to repeat any/all of this, I
have no basis for complaint;
- Searched for strategies via Google and the mailing list archives, and my
impression is this is not going to be easy;
- Found a passing mention of something like "Tom Lane's data reader
unsure of name" but have been unable to locate this utility.
Recasting Q2 above: Does it exist?
- Started a 9.2 installation on the dedicated drive to house the
restored/recreated data, which leads to a "good practice"
supplementary question:
Is there a convention for the second port number, e.g., 6543 as per examples?

Thank you for your time.

Regards
Gavan Schneider

[Tom Lane]

Post by Temp key: basic, via spamcop
OSX LIon Server 10.7.5; system supplied PostgreSQL 9.0.5;
user data on dedicated drive via TABLESPACE,
i.e., keeping my stuff away from the OS data tables
Backup via TimeMachine on hourly basis (i.e., file system backup)
TODO: included determining a "better way" of backing up but
delayed/inhibited by concerns that restoring pg_dump files would
have to restore the OS defined tables which did not seem to be the
correct thing to do.
Dedicated drive (Mac Mini Server 2x 500G: internal drive) failed,
and computer returned to Apple for warranty repair.
Another computer was reconfigured by restoring from backups, and all files returned,
Except I had trouble getting a suitable drive for the TABLESPACE data (rural location).
Once the TABLESPACE files were restored and the database
stopped/started etc. they were not mounted/recognized by postgres.

Yeah, that's not gonna work. There are two big things in your way (and
maybe some smaller ones I'm forgetting):

1. The catalog information describing your tables would not have been in
the tablespace directory; it would've been in the system catalog tables,
which were on the main drive in the scenario you've described. So even
after doing a CREATE TABLESPACE, the database server wouldn't know
what's out there.

2. The XID counter of the new installation is nowhere near what it
likely was in the old one. So even if you got past #1, all the tables
would probably appear to be empty because their contents are all "in the
future" according to the MVCC rules.

Tablespaces are handy for spreading your data across more than one
physical filesystem, but they're no substitute for proper backup
procedures. However, given that you said you had Time Machine backups,
you might be able to get someplace by restoring both your own tablespace
and the system database files from those backups. Be sure to restore
all of this stuff from the same TM snapshot, and if possible pick a
snapshot where the database would've been totally idle while TM was
running. The key thing to make this work is that all files belonging
to the database have to be in sync, no matter which tablespace they were
in. You don't have to overwrite the system database files if you'd
rather not: just restore all these files somewhere where you have enough
space, fix the tablespace symlink appropriately, and then fire up a
second 9.0.x postmaster pointing it at the restored main data directory.
That should get you to a point where you can pg_dumpall and restore into
your new 9.2 postmaster. (For safety's sake I'd recommend a dump and
restore rather than trying to do something like an upgrade-in-place.)

Post by Temp key: basic, via spamcop
- Started a 9.2 installation on the dedicated drive to house the
restored/recreated data, which leads to a "good practice"
Is there a convention for the second port number, e.g., 6543 as per examples?

+1 for running a second installation as a better practice for this.
I've heard 5433 (one more than the default) as a common
second-postmaster port, but there's not really any convention.

regards, tom lane

[Temp key: basic, via spamcop]

Thank you Tom, for your reply on Sunday, November 11, 2012.

... There are two big things in your way (and maybe some smaller ones
1. The catalog information describing your tables would not have been in
the tablespace directory; it would've been in the system catalog tables,
which were on the main drive in the scenario you've described. So even
after doing a CREATE TABLESPACE, the database server wouldn't know
what's out there.

Since "CREATE TABLESPACE" is a PostgreSQL extension I would love
to see the concept fully populated with statements along the
lines of:
MOUNT TABLESPACE <name> [AT <file system location>]
(default is prev. known location)
UNMOUNT TABLESPACE <name> [IF EXISTS] (data is preserved)

(Or the TABLESPACE concept could get folded back onto the
DATABASE syntax along the lines above, so they have a robust
representation in the file-system which can be moved/removed/returned.)

I feel the TABLESPACE data instance should be robust and much
more self-contained. With this in place you could get radical
and be able to walk data around on a (thumb) drive, and remove
it after use. Restoration and back-up strategies also become a
lot less complicated, and should be much more reliable. (I do
have some insight into how many things "under the hood" would
have to change so I'm not holding my breath on this. :)

Is this a sufficient mention for a "feature request" or do I
need to repeat it in a more formal manner?

2. The XID counter of the new installation is nowhere near what it
likely was in the old one. So even if you got past #1, all the tables
would probably appear to be empty because their contents are all "in the
future" according to the MVCC rules.

I really can't fully understand what is happening here. I have
walked my system through time with both "surgical" restoration
of the main data directory and contemporaneous tablespace files;
and, full "point in time" restoration of the entire system. No
joy. Files present, fully restored but no recognition of the
data in the TABLESPACE by postgres.

I have learnt a lot about the OSX installation of PostgreSQL.
(And I need to get a faster backup drive, each full restoration
cycle took ages :-(

As a simple person I still can't figure why the db engine
doesn't use the TABLESPACE when the relevant OID is well in the
past since it would seem to me that the main table already has
all the defining information. Or does the engine drop this
information if inconsistencies are found?

Tablespaces are handy ...

but, to my mind are still too fragile.


Anyway, my last chance before recreating the data from scratch...

Is there an extraction tool that could pull data directly from
the TABLESPACE files?

Any other ideas?


Thanks again everyone for your time.

Regards
Gavan Schneider

[Tom Lane]

Post by Temp key: basic, via spamcop
Since "CREATE TABLESPACE" is a PostgreSQL extension I would love
to see the concept fully populated with statements along the
MOUNT TABLESPACE <name> [AT <file system location>]
(default is prev. known location)
UNMOUNT TABLESPACE <name> [IF EXISTS] (data is preserved)

That is quite unlikely to happen, unfortunately. For that to work,
tablespaces would have to be completely independent, ie each with its
own system catalogs, WAL stream and XID counter. Quite aside from any
efficiency problems with that, it'd be impossible to guarantee atomic
commit for any transaction that changed data in more than one
tablespace, since there's no way to be sure that commit records in
multiple WAL streams would all reach disk together. This is pretty much
the same reason why we don't have anything like mount/dismount for
databases --- a database does have its own system catalogs, which would
address the first part of the problem, but it still shares WAL and XID
infrastructure with the other databases in the installation.

Post by Temp key: basic, via spamcop
I really can't fully understand what is happening here. I have
walked my system through time with both "surgical" restoration
of the main data directory and contemporaneous tablespace files;
and, full "point in time" restoration of the entire system. No
joy. Files present, fully restored but no recognition of the
data in the TABLESPACE by postgres.

Hard to tell what's going wrong on the basis of that much information.
If you have matching backup copies of the main data directory and the
tablespace directory, then in principle it should work, but I can't
debug "no joy".

regards, tom lane

[Gavan Schneider]

Post by Tom Lane

Post by Temp key: basic, via spamcop
Since "CREATE TABLESPACE" is a PostgreSQL extension I would
love to see the concept fully populated with statements along
MOUNT TABLESPACE <name> [AT <file system location>] (default
is prev. known location)
UNMOUNT TABLESPACE <name> [IF EXISTS] (data is preserved)

That is quite unlikely to happen, unfortunately. For that to work,
tablespaces would have to be ...

And I'm not holding my breath. :)

As I thought, and you have confirmed, there are many reasons why
implementing this proposal would be hard. While I still think
there are reasons to consider it, I'm way too ignorant to know
whether the cost/benefit could ever favour such a change.

Post by Tom Lane
Hard to tell what's going wrong ... it should work, but I can't debug
"no joy".

Agree. Part of the problem is I don't know the system well
enough to dig for relevant clues (still learning), and since I
didn't setup the core system in the first place there's extra difficulties.


Finally, while I expect the answer is no, I need to repeat this question:

Is there an extraction tool that could pull data directly from
the TABLESPACE files?

Many thanks,
Gavan Schneider

[Gavan Schneider]

On Sunday, November 11, 2012 at 23:47, ...
<http://archives.postgresql.org/pgsql-novice/2012-11/msg00009.php>

I wrote about my problems restoring a datavase from
point-in-time file system backups in the presence of a
TABLESPACE. Basically the main part of the cluster worked fine
but the data in the TABLESPACE was not readable.

The advice from Tome Lane was along the lines of it could work
if the files are in sync, and for me to plan on doing better backups.

Complete agreement on the second point, but proving him right on
the first has taken a fair bit of work.

The following relates to OS X Server 10.7.5 (Lion) but I am
guessing some of it will be applicable to later versions.

OS X Server uses PostgreSQL (9.0.5) for several system related
duties and the temptation is to use an installed and working db
server to do other duties. A TABLESPACE seemed a good way to
keep system data separated from user data. The TABLESPACE on a
dedicated drive also seemed reasonable. Until the drive died and ...

So, some system specific factoids:

1. The system's PostgreSQL specific user on OSX is _postgres.
This is a user to which I could not su (no idea why not), so
first thing is to create a dedicated user, e.g., postgres, so
you can run postgres/postmaster as this user.

2. Restoring files via multiple modes appears to be problematic
with TABLESPACES. Time Machine does a very good job but this
"corner case" has problems. If the restoration was done
system-wide to the selected point-in-time and the machine
restarted the pg_tblspc link was gone by the time I got to look
at the filesystem (plus my TABLESPACE was not listed). The link
is present in the backup. Doing the restoration "surgically",
i.e., only the relevant files (with postgres stopped), the link
remains in place, and the TABLESPACE is still listed, but none
of its contents are recognised. This is solved by restoring data
via the command-line utility into its own clean area, hand
adjusting the symbolic link, and using another instance of
postgres/postmaster, i.e., restored data is run in its own
cluster. (Tom did say something like this but I didn't read it
that way until now. :)

3. The location information in table pg_tablespace (splocation)
appears to be ignored, and is not updated when the TABLESPACE
data is fully recognized. Leave as is, and, note this column
been removed as of 9.2

4. On OS X /usr/bin/postgres is not postgres(1) rather it is a
ruby script which waits for the file system to be fully
functional before invoking /usr/bin/postges_real. (Obvious in
retrospect, but it had me very confusing for a while. I don't
normally do "cat" on known binary files!)

5. The command line is your friend esp. the Time Machine utility (/usr/bin/tmutil)

6. Once the restored TABLESPACE data is running in the new
cluster, you will find it has all the system stuff in there and
has lots of legacy ownership issues from when it was the system
database. Basically get your data out and shut it down.

7. Despite articles to the contrary I now think the best advice
is running a separate instance of postgres/postmaster (which
allows you to pick the version you want), obviously on its own
port, with its own backup process.
(Note to Apple: please use a non-standard port for your "under
the hood" database. Also /usr/bin/postgres should be left as is,
and the ruby script renamed, e.g., postgres_startup. This would
allow customers to do things according to the manual.)


The following is my (cleaned-up) log of what worked. No more and
no less... use as you wish, and my commiserations if you need to
do this yourself ...

Regards
Gavan

=============================================
gavan$ cd /Volumes/SQL/work # get into the sandbox
gavan$ man tmutil # read the instructions! AND you need root
access for the rest
gavan$ sudo tmutil restore /Volumes/Time\ Machine\
Backups/Backups.backupdb/computer/2012-10-30-001840/HD2/MyData .
Password:
Total copied: 22.27 MB (23350176 bytes)
Items copied: 882
gavan$ sudo tmutil restore /Volumes/Time\ Machine\
Backups/Backups.backupdb/computer/2012-10-30-001840/HD/var/pgsql .
Total copied: 76.98 MB (80715013 bytes)
Items copied: 2136
gavan$ ls -ls
total 0
0 drwx------ 3 _postgres staff 102 Sep 22 16:50 MyData
0 drwx------ 12 _postgres _postgres 612 Oct 21 14:14 pgsql
gavan$ sudo ls -ls pgsql/pg_tblspc/
total 8
8 lrwx------ 1 _postgres _postgres 29 Sep 22 16:50 59580 ->
/Volumes/HD2/MyData # Identify the OID
gavan$ sudo ln -sfF /Volumes/SQL/work/PendariData ./pgsql/pg_tblspc/59580
gavan$ sudo ls -ls pgsql/pg_tblspc/
total 8
8 lrwxr-xr-x 1 root _postgres 37 Nov 17 21:19 59580 -> /Volumes/SQL/work/MyData
gavan$ sudo chown -R postgres: pgsql PendariData # can't use _postgres
gavan$ sudo chown gavan:_postgres . # postgres is grouped into
_postgres, my choice only
gavan# sudo bash
bash-3.2# su postgres
bash-3.2$ nohup /usr/bin/postgres_real -D
/Volumes/SQL/work/pgsql \
-> --unix_socket_directory=/var/pgsql_socket
--listen_addresses=127.0.0.1 \
-> -p 5433 >logs 2>&1 </dev/null &
[1] 50096
bash-3.2$ exit
exit
bash-3.2# cat ./logs
LOG: database system was interrupted; last known up at
2012-10-30 00:14:50 EST
LOG: database system was not properly shut down; automatic
recovery in progress
LOG: consistent recovery state reached at 0/5C9C26A0
LOG: redo starts at 0/5C9C1780
LOG: record with zero length at 0/5C9D4548
LOG: redo done at 0/5C9D4508
LOG: last completed transaction was at log time 2012-10-30 00:18:33.922996+11
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
bash-3.2# exit
exit
gavan$ cd ..; pwd; ls -ls ./work
/Volumes/SQL
total 8
0 drwx------ 3 postgres staff 102 Sep 22 16:50 MyData
8 -rw-r--r-- 1 postgres _postgres 487 Nov 18 08:42 logs
0 drwx------ 12 postgres _postgres 612 Nov 18 08:41 pgsql
0 drwxr-xr-x 2 postgres _postgres 68 Nov 18 08:26 test
gavan$ psql -p 5433
psql (9.0.5)
Type "help" for help.

gavan=> \l
List of databases
Name | Owner | Encoding | Collation | Ctype
| Access privileges
-------------------+-------------+----------+-----------+-------+-------------------------
Accounts | book_keeper | UTF8 | C | C |
Farm | gavan | UTF8 | C | C |
caldav | caldav | UTF8 | C | C |
collab | collab | UTF8 | C | C |
device_management | _devicemgr | UTF8 | C | C |
gavan | gavan | UTF8 | C | C |
postgres | _postgres | UTF8 | C | C |
rosebud | rosebud | UTF8 | C | C |
roundcubemail | roundcube | UTF8 | C | C |
template0 | _postgres | UTF8 | C | C
| =c/_postgres +
| | | |
| _postgres=CTc/_postgres
template1 | _postgres | UTF8 | C | C
| =c/_postgres +
| | | |
| _postgres=CTc/_postgres
(11 rows)

gavan=> \q
gavan$ pg_dumpall -O -x -U _postgres --port=5433
--no-tablespaces \
-> --inserts --file=./work/restored.sql
gavan$ pg_dump -O -x -U _postgres --port=5433 --no-tablespaces
--inserts \
-> --file=./work/accounts.sql Accounts
pendari:Rosebud.SQL gavan$ ls -ls ./work/
total 2656
0 drwx------ 3 postgres staff 102 Sep 22 16:50 MyData
640 -rw-r--r-- 1 gavan _postgres 326424 Nov 18 10:44 accounts.sql
8 -rw-r--r-- 1 postgres _postgres 1290 Nov 18 10:20 logs
0 drwx------ 12 postgres _postgres 612 Nov 18 08:41 pgsql
2008 -rw-r--r-- 1 gavan _postgres 1026775 Nov 18 10:35 restored.sql
gavan$ bbedit accounts.sql # etc ....
=============================================