Restore PostgreSQL database (or db names) to new version, from files?

Question

Scenario in short:

• a development laptop broke
• old HDD is still readable
• new laptop has PostgreSQL 9.4 instead of 9.1
• both laptops use a flavor of Ubuntu Linux

Question 1: Is my understanding correct that in order to restore the old data, I would need a server with the same major+minor version as the one that wrote the old data directory? Since no 9.1 packages are available for my distribution, I would have to compile a v9.1.x server from source, copy over the old data, start the server and perform a normal pg_dump, which could then be restored to the new cluster?

Question 2: There were around 10-15 databases on the old laptop, but since it was a development machine, in theory all of the data should be replacable, apart from some local experiments. I'm thinking of just scrapping the old data, but I can't remember with 100% certainty what those databases were. Is there a way to extract some basic information (such as database names, maybe even sizes or timestamps) from the old data directory without running a 9.1 server?

Answer 1

Looks like you've figured out question 1 for yourself already (short answer: yes, use the latest 9.1.x release, and make sure the compile-time options are the same between the version on the old and new machine to be sure the data directory, and the machines should ideally be as similar as possible in order to be binary-compatible, e.g. both x86-64, similar glibc versions, etc.).

But about question 2:

Is there a way to extract some basic information (such as database names, maybe even sizes or timestamps)

You can look under the "base" subdirectory of the data directory, and you should see something like this:

/datadir/base $ du -sh *
6.5M    1
6.1M    12292
6.5M    12297
 39M    3237152
6.3M    371336
 10M    4049006
 41M    4481732
6.7M    4691247
 10M    4927721
7.2M    4927722
7.4M    58068
  0B    pgsql_tmp

Each of those directories with an integer as the directory name represents a database inside my PostgreSQL cluster. The integers (OIDs) in the directory name match the oid you would see from a query like:

 SELECT oid, datname FROM pg_database;

if you had the server running. I don't know of a trivial way to determine the database name from those OIDs without having the server running, but at least you know how many databases there are and how big they should be. And you should be able to figure out creation time too from checking stat. In my case, "1" was for "template1", "12292" was "template0", and the rest were various other databases.

Answer 2

I just came across a way to find out which databases the old PG_DATA directory contained. It's a bit hackish, and too late for me, but it might be useful for somebody else in the future.

Information about database names (etc) is not stored in PG_DATA/base, but rather in PG_DATA/global. The files in that directory are (in my case, at least) small enough to be inspected with hex editors or command line tools like grep and strings:

$ cd /usr/local/pgsql-9.1/data/global
$ grep template1 *
Binary file 59402186 matches
Binary file 59402192 matches
$ ls -la 59402186 59402192
-rw------- 1 postgres postgres 8.0K Nov 25 09:50 59402186
-rw------- 1 postgres postgres  16K Nov 25 09:50 59402192

The name of one of the default internal databases, "template1", occurs as plain text string in only two small files. Looking for other strings in those files reveals the other database names:

$ strings 59402186 | sort | uniq
blank0
blank1
de_AT.iso88591
de_AT.utf8
deepend
en_US.utf8
footest
channelwerk
lnfbase
lnfbase10
mok_db_bio
postgres
tin_phoenix
sunn
sunn_bkp20151031
template0
template1
upside

Removing the standard PG databases and the charset names, this leaves the list of database names I was originally looking for:

deepend
footest
channelwerk
lnfbase
lnfbase10
mok_db_bio
tin_phoenix
sunn
sunn_bkp20151031
upside