upgrading to postgresql 9.1

[Joseph_sys]

Are there any easy way to upgrade between postgresql versions 9.0 to 9.1
From my notes:

First, make sure you backup all your databases:
pg_dumpall -U postgres > postgres-backup.dump

emerge virtual/postgresql-base virtual/postgresql-server
emerge --config =dev-db/postgresql-server-9.1.1
/etc/init.d/postgresql-9.1 start

Do I need to restore the databases?

[Joseph_sys]

I've found some decent instructions
http://programminglinuxblog.blogspot.co ... entoo.html

but apparently pg_upgrade91 doesn't work as gentoo file are in /etc and pg_upgrade91 expects them in the data directory

[titanofold]

You know, you could just read the official Gentoo Docs.

http://www.gentoo.org/doc/en/postgres-h ... #doc_chap5

[Joseph_sys]

I've followed those instruction, but it seems to me they are not complete:

Code: Select all

mkdir /var/lib/postgresql/9.1/
mkdir /var/lib/postgresql/9.1/data
chown postgres:postgres /var/lib/postgresql/9.1/data/
ln -s /etc/postgresql-9.0/*.conf /var/lib/postgresql/9.0/data/
ln -s /etc/postgresql-9.1/*.conf /var/lib/postgresql/9.1/data/

su - postgres
postgres@syscon2 ~ $ pg_upgrade -u postgres \
> -d /var/lib/postgresql/9.0/data -D /var/lib/postgresql/9.1/data \
> -b /usr/lib/postgresql-9.0/bin/ -B /usr/lib/postgresql-9.1/bin
Performing Consistency Checks
----------------------------- 
Checking old data directory (/var/lib/postgresql/9.0/data)  ok
Checking old bin directory (/usr/lib/postgresql-9.0/bin)    ok
Checking new data directory (/var/lib/postgresql/9.1/data)  ok
Checking new bin directory (/usr/lib/postgresql-9.1/bin)    ok

This utility can only upgrade to PostgreSQL version 9.0.

The last statement: This utility can only upgrade to PostgreSQL version 9.0.
I'm upgrading from 9.0 to 9.1, is it significant?

The data databases did not get transfered. I have tired to transfer the data but I get an error:
You must have read and write access in the current directory.

Code: Select all

pg_upgrade91 -v --old-datadir=/var/lib/postgresql/9.0/data/ --new-datadir=/var/lib/postgresql/9.1/data --old-bindir=/usr/lib/postgresql-9.0/bin/ --new-bindir=/usr/lib/postgresql-9.1/bin/
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 
You must have read and write access in the current directory.

data - directory is owned by "postgres" so why it is complaining about not able to read/write?

Code: Select all

ls -al /var/lib/postgresql/9.1/
total 12
drwx------ 13 postgres postgres 4096 Dec  4 18:20 data

[titanofold]

"You must have read and write access in the current directory."

Which directory were you running pg_upgrade91 in?

[Joseph_sys]

I did not check which directory I was IN but when I do "su - postgres" it places me in: /var/lib
should I be in: /var/lib/postgresql ?

postgresql has permission:
drwxr-xr-x 7 postgres postgres 4096 Nov 27 23:23 postgresql

I'll be upgrading another server most likely tomorrow.

[Joseph_sys]

OK, the sequence that worked for me is:

Code: Select all

 su - postgres
postgres@syscon4 ~ $ pg_upgrade -u postgres -d /var/lib/postgresql/9.0/data -D /var/lib/postgresql/9.1/data -b /usr/lib/postgresql-9.0/bin -B /usr/lib/postgresql-9.1/bin
Performing Consistency Checks
-----------------------------
Checking old data directory (/var/lib/postgresql/9.0/data)  ok
Checking old bin directory (/usr/lib/postgresql-9.0/bin)    ok
Checking new data directory (/var/lib/postgresql/9.1/data)  ok
Checking new bin directory (/usr/lib/postgresql-9.1/bin)    ok

This utility can only upgrade to PostgreSQL version 9.0.

Question.
Is the old databases from 9.0/data suppose to be transfered to new directory 9.1/data ?
As I'm getting only basic databases (not all of them got transfered - the one I created).

Code: Select all

psql -U postgres -l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

[titanofold]

Code: Select all

This utility can only upgrade to PostgreSQL version 9.0.

Use pg_upgrade91 or set 9.1 as the default slot.

Code: Select all

postgresql-config set 9.1

[risa2000]

I just want to add that on new gentoo (probably udev, but I am not sure what exactly triggered the change) the local sockets are expected in /run/postgresql but for some reason some tools from the package stick to /var/run/postgresql.
I tried to set unix_socket_directory but neither the former nor the latter helped, so finally I needed to "ln -s /var/run/postgresql /run" to be able to run pg_upgrade91.