current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
    
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 2 days ago
    
@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 2 days ago
    
Interesting, I see postgresql-9.1 in my output of apt-cache search postgresql... –  dezso 2 days ago
    
@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 2 days ago

2 Answers 2

up vote 3 down vote

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

share|improve this answer
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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