On going from postgres 9.1 to 9.2, it says that the data bases need to be upgraded so on giving the upgrade command I give and get the following
$ sudo service postgresql upgrade
Stopping postgresql service: [ OK ]
Upgrading database: [FAILED]
See /var/lib/pgsql9/pgupgrade.log for details.
The contents of the log file /var/lib/pgsql9/pgupgrade.log
are
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
connection to database failed: fe_sendauth: no password supplied
could not connect to old postmaster started with the command:
"/usr/lib64/pgsql/postgresql-9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql9/data-old" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql9'" start
Failure, exiting
I used the postgres with password enable something like this pg_connect("host=localhost port=5432 dbname=pgdbname user=postgres password=postgrespassword");
I had yum update
d my system and fell into this. Can you even suggest a way in which I can revert back everything then it will be great!
Best Answer
Workaround
Edit your
pg_hba.conf
and set connections onlocal
totrust
for the duration of the upgrade.Background
Amazon's Amazon Linux team have made the bizarre decision to do a major version upgrade of PostgreSQL on a simple "yum update". Most vendors use different package names for different major versions so users have to make the explicit decision to upgrade. Amazon has not done so and do not take feedback about this issue seriously.
Upgrading users to a new PostgreSQL version on "yum update" is the wrong thing to do for exactly the reason you've encountered:
pg_upgrade
has limitations, and you can't do a dump and reload unless you have the old server version's binaries around.I wrote a note about this a while ago.
The Amazon Linux FAQ entry on this topic has improved since I wrote about it, but still doesn't acknowledge that they're creating the problem with their bad packaging policies.
Seriously consider using a distro that takes more care with packaging PostgreSQL. The PGDG team isn't producing packages for Amazon Linux at this point, otherwise I'd recommend using them.