3

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?

Improve this question
4
  • 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.
    – a_horse_with_no_name
    Mar 30 '15 at 6:10
  • @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.
    – Craig Ringer
    Mar 30 '15 at 8:38
  • Interesting, I see postgresql-9.1 in my output of apt-cache search postgresql...
    – dezso
    Mar 30 '15 at 11:19
  • @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.
    – ako
    Mar 30 '15 at 14:05
5

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

Improve this 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.