Re: RESOLUTION: Restoring data from TABLESPACE files

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 ....
=============================================