34

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.

Alvos
  • 561
  • 3
  • 7
  • 15

6 Answers6

25

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.

Konrad Garus
  • 53,145
  • 43
  • 157
  • 230
  • 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 ;) – Alvos Jul 22 '10 at 05:47
  • 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. – Alvos Jul 24 '10 at 15:36
  • 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. – Nordes Oct 18 '11 at 08:53
  • 2
    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. – Epigene Apr 11 '18 at 12:27
  • 2
    @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 – Ahtisham Mar 30 '19 at 15:58
  • 1
    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/ – PatrickReagan Jan 24 '20 at 02:12
  • 1
    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. – PatrickReagan Jan 30 '20 at 23:54
12

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

kolufild
  • 712
  • 1
  • 9
  • 20
  • 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. – pabloferraz Jul 01 '16 at 15:10
  • 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! – Abhishek Shah Jan 24 '21 at 19:01
0

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';
Zeki Gürsoy
  • 11
  • 4
  • all right, except by correct table name: pg_catalog.pg_database – alijunior Jan 26 '18 at 15:33
  • 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 – khanna Aug 29 '20 at 14:32
0

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

modify registry

sbgib
  • 5,580
  • 3
  • 19
  • 26
haoming
  • 1
0

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

Ghandhikus
  • 839
  • 2
  • 9
  • 12
-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
Matthieu
  • 2,736
  • 4
  • 57
  • 87
user2615084
  • 1
  • 1