Recover postgreSQL databases from raw physical files

Question

I have the following problem and I need to know if there´s a way to fix it.

I have a client who was cheap enough to decline buying a backup plan for his postgreSQL databases on the main system that runs his company and as I thought it would happen some day, some OS files crashed during a blackout and the OS needs to be reinstalled.

This client didn't have any backups of the databases but I managed to save the PostgreSQL main directory. I read that the databases are stored somehow inside the data directory of the postgres main folder.

My question is: Is there any way to recover the databases from the data folder only? I am working in a windows environment (XP service pack 2) with PostgreSQL 8.2 and I need to reinstall PostgreSQL in a new server. I would need to recreate the databases in the new environment and somehow attach the old files to the new database instances. I know that's possible in SQL Server because of the way that engine stores the databases but I have no clue in postgres.

Any ideas? They would be much appreciated.

Disquisition

Answer 1

If you have the whole data folder, you have everything you need (as long as architecture is the same). Just try restoring it on another machine before wiping this one out, in case you didn't copy something.

Just save the data directory to disk. When launching Postgres, set the parameter telling it where the data directory is (see: wiki.postgresql.org). Or remove original data directory of the fresh installation and place the copy in its place.

Merbromin

wow, that seems very easy, let me give it a try tomorrow and I'll let you know the result. Thanks for the quick answer ;) – Disquisition

I tried that and it should work but for I think the data folder was corrupted because it didn't work. It should when the data folder is ok though. I had transaction files as a backup and it will take me longer to recover the historical data but I think I will be able to recover everything. It will just take more time than I hoped. Thank you for your help. I tried your solution in a test environment and it does work. – Disquisition

Thank you :). It worked fine. You only need to remember that the passwords for accounts are those from the old install and not the new. – Impersonate

Like others have mentioned, remember that the copied files must have correct permissions. On linux systems they usually must be owned by postgres user so something like sudo chown -R postgres:postgres /var/lib/postgresql should work. – Wernsman

@KonradGarus I tried replacing the data folder after stopping the postgresql services from the task manager in windows but when i try to open my psql shell It crashes with error "Could not connect to server. Connection refused. Is the server running on local host and accepting connection on port 5432". Please help – Kachine

This worked for me, but to be totally clear: "The whole data folder" means not only the base/ folder but the entire parent folder that includes base/. For my OS-X server is was: /usr/local/var/postgresql@11/ – Costive

Tried for fun: this even works going from OS-X (10.13.6) to Ubuntu (18.04), both using postgres 11. As stated elsewhere here, file ownership needs to work with what the receiving machine is expecting. – Costive

Answer 2

This is possible, you just need to copy the "data" folder (inside the Postgres installation folder) from the old computer to the new one, but there are a few things to keep in mind.

First, before you copy the files, you must stop the Postgres server service. So, Control Panel->Administrative tools->Services, find Postgres service and stop it. When you're done copying the files and setting permissions, start it again.

Second, you need to set the permissions for the data files. Because postgres server actually runs on another user account, it will not be able to access the files if you just copy them into the data folder, because it will not have permissions to do so. So you need to change the ownership of the files to the "postgres" user. I had to use subinacl for this, install it first, and then use it from command prompt like this (first navigate to folder where you installed it):

subinacl /subdirectories "C:\Program Files\PostgreSQL\8.2\data\*" /setowner=postgres

(Changing ownership should also be possible to do from the explorer: first you must disable "Use simple file sharing" in Folder options, then a "Security" tab will appear in the folder Properties dialog, and there are options there to set permissions and change ownership, but I wasn't able to do it that way.)

Now, if the server service can't start after you start it manually again, you can usually see the reason in the Event viewer (Administrative tools->Event viewer). Postgres will throw an error event, and inspecting it will give you a clue about what the problem is (sometimes it will complain about a postmaster.pid file, just remove it, etc.).

Dogvane

It really works,I only had a problem with the pg_hba.conf file, but commenting the IPv6 line did the trick, thank you and sorry for my bad english. – Secondrate

This works -> I only had the data folder of parent directory. After installation -> ==>0. STOP the pg service ==>1. renamed original data folder to _data. ==>2. Copy the old data folder to the installation folder C:\Program Files\PostgreSQL\{old_version}. ==>3. Open permissions of _data folder. ==>4. make modifications of permission to data folder replicating all from the _data folder's permission. ==>5. START the pg service back on. ==>6. Connect the database. It should work! – Hyalo

Answer 3

The question is very old, but I want to share an effective method that I found.

If you have not got a backup with "pg_dump" and your old data is folder, try the following steps. In the Postgres database, add records to the "pg_database" table. With a manager program or "insert into". Make the necessary check and change the following insert query and run it.

The query will return an OID after it has worked. Create a folder with the name of this number. Once you have copied your old data into this folder, the use is now ready.

    /*
    ------------------------------------------
    *** Recover From Folder ***
    ------------------------------------------
    Check this table on your own system.
    Change the differences below.
    */
    INSERT INTO
      pg_catalog.pg_database(
      datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn,
      datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl)
    VALUES(
                             -- Write Your collation  
      'NewDBname', 10, 6, 'Turkish_Turkey.1254', 'Turkish_Turkey.1254',
      False, True, -1, 12400, '536', '1', 1663, Null);

    /*
    Create a folder in the Data directory under the name below New OID.
    All old backup files in the directory "data\base\Old OID" are the directory with the new OID number
    Copy. The database is now ready for use.
    */
    select oid from pg_database a where a.datname = 'NewDBname';

Berke

all right, except by correct table name: pg_catalog.pg_database – Zeigler

didn't work - I have in old backup, folder "25559", but when I create a new database, or even try an INSERT COMMAND Like above, and the oid is 16384. Neither renaming base/25559 -> base/16384 Nor set oid = 25559 where oid = 16384 helps. Postgresql 12, CENTOS 7 – Shortage

Answer 4

As shown by move database to another hard drive. All we need to do is to modify the registry table and file permissions. By modifying registry table(shown in image 1), postgresql server know the new location of data.

modify registry

Bobbinet

Answer 5

If you have issues with permissions or with stuff like icacls during installation to old data folder then try my solution from sister website.

https://superuser.com/a/1611934/1254226

Twophase

Answer 6

I do so but the most tricky part was to change the owner permission:

1. go to services from administative tools
2. find postgres service and double click on it
3. at log on tab change to local system
4. then restart

Unearth