Обсуждение: Restoring data from TABLESPACE files
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
"Temp key: basic, via spamcop" <pg-gts@snkmail.com> writes: > 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. 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.) > - 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? +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
Thank you Tom, for your reply on Sunday, November 11, 2012. Tom Lane wrote: > >... 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. > 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
"Temp key: basic, via spamcop" <pg-gts@snkmail.com> writes: > 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) 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. > 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
On Tuesday, November 13, 2012 at 14:30, Tom Lane wrote: >Gavan Schneider writes: >>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) > >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. >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
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 .... =============================================