Have: Postgres database files. Need: Functioning database

Question

I am running Ubuntu 14.04 with a postgres/postgis setup and in a weak moment removed PostgreSQL 9.1 (seemingly a legacy install from 12.04 from before I upgraded to 14.04), having erroneously convinced myself my current postgis setup relied on 9.3 alone.

That was not the case, and to make matters worse, I removed it while the instance was running, so I headed to the repositories to restore 9.1.

The repositories for Ubuntu don't have PostgreSQL 9.1 for 14.04, so I instead compiled from source so I could run pg_dump or pg_upgrade, which requires binaries from both.

But my issue then was that just because I now had a working version of 9.1 again, that didn't make my databases from earlier visible.

So, as far as I am aware, my database files are intact, but I need to 'hook' them up with the new 9.1 service I installed today. I hope it is trivial, but it has stumped me for hours. Any pointers for where to start with updating the config?

• user1822: I don't know Ubuntu, but with a "vanilla" Postgres all you need to do is point pg_ctl to the right data directory, e.g. pg_ctl -D /path/to/database/file start.
• Craig Ringer: @a_horse_with_no_name on Ubuntu when using Pg from packages you should generally use pg_wrapper to manage Pg - pg_ctlcluster, pg_createcluster, etc. For hand-compiled it's like any other Pg.
• András Váczi: Interesting, I see postgresql-9.1 in my output of apt-cache search postgresql...
• ako: @dezso I I get this msg"--Package postgresql-9.1 is not available, but is referred to by another package. This may mean that the package is missing, has been obsoleted, or is only available from another source." But I don't have the PGDG ppas added. That would probably have saved me some trouble.

Answer 1

@a_horse_with_no_name is quite right. Because you compiled PostgreSQL from source, the usual tools provided by pg_wrapper like pg_ctlcluster and pg_lsclusters are not available. As a result the PostgreSQL init scripts on Ubuntu won't start your PostgreSQL 9.1 DB.

You could hook your custom binaries into pg_wrapper, but it's not worth the hassle for a one-off.

Assuming your 9.1 data directory is still owned by user postgres and in the default location:

• sudo -u postgres -i
• PGPORT=5440 PATH=/path/to/your/9.1_install/bin pg_ctl -D /path/to/your/9.1/db
• PGPORT=5440 /path/to/9.3/bin/pg_dump -Fc -f mydb.pgdump mydb
• PGPORT=5440 /path/to/9.3/bin/pg_dumpall --globals-only > my_91_globals.sql

then run my_91_globals.sql against your 9.3 server, or examine it and make any required changes.

Then pg_restore the mydb.pgdump using the pg_restore from 9.3.

You might need to adjust this if you're working from a restored backup copy of your 9.1 datadir, because its permissions may not be those of the postgres user. It does not matter what user owns it, but you might need to tweak pg_hba.conf if you're using peer auth and connecting as a different user.

Answer 2

Another approach which should work for you is to install PostgreSQL from the PostgreSQL Global Development Group's APT repository.

They provide compiled versions of PostgreSQL for all supported versions of PostgreSQL and all supported versions of Ubuntu (and of course some versions of Debian).

https://wiki.postgresql.org/wiki/Apt is the starting point for this.