1

I need to get databases from the old PostgreSQL folder that was in Program Files (win 10).

I tried changing the data path through the registry (ImagePath value after "-D" at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postgresql-x64-12) from the current to the old PostgreSQL data folder.

I also tried replacing files in the current folder from PostgreSQL with old ones (replaced data for C:\Program Files\PostgreSQL\12\data\base and C:\Program Files\PostgreSQL\12\data\global).

In both cases, an error occurred when trying to start the postgresql-x64-12 service. Error message: the postgresql-x64-12 service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

How can I get databases from old ../Program Files/PostgreSQL folder?

Thirumal
  • 8,280
  • 11
  • 53
  • 103
Dmitriy
  • 49
  • 1
  • 6

1 Answers1

2

After replacing the folders C:\Program Files\PostgreSQL\12\data\base and C:\Program Files\PostgreSQL\12\data\global with the old ones and attempts to start the postgresql-x64-12 service, a log file appeared with the following content:

LOG: database system was shut down at ...
LOG: invalid primary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 17452) was terminated by exception 0xC0000409
HINT: See C include file "ntstatus.h" for a description of the hexadecimal value.
LOG: aborting startup due to startup process failure
LOG: database system is shut down

Because of the message PANIC: could not locate a valid checkpoint record, I used pg_resetwal: in PowerShell I opened the folder C:\Program Files\PostgreSQL\12\bin, then ran the command .\pg_resetwal.exe -f -D "C:\Program Files\PostgreSQL\12\data. In some cases, it is worth using the command without -f, also for other versions of Postgres a different command may be needed. More information: https://stackoverflow.com/a/8812934/14145682.

After that, the service also did not start, and the following error was present in the log:

FATAL: could not access status of transaction
DETAIL: Could not read from file "pg_multixact/members/0000" at offset 8192: read too few bytes.

So, I replaced the files in the C:\Program Files\PostgreSQL\12\data\pg_multixact folder with the old ones.

After these steps, the postgresql-x64-12 service started without errors and it was possible to restore the old database.

Dmitriy
  • 49
  • 1
  • 6
  • Lesson learned: don't mess with the DBMS's data files. These are managed by the DBMS, (and the format may depend on Postgres's Major version number) Nothing to see there. – wildplasser Aug 22 '20 at 12:08