Re: Upgrading using pg_dumpall

On 09/03/2016 02:44 PM, Rich Shepard wrote:
> On Sat, 3 Sep 2016, Adrian Klaver wrote:
>
>> No it says you are using the 9.4.5 version of psql to connect to a
>> 9.3.4 server. psql is available independent of the server running. You
>> will need to see if both servers are running by doing something like:
>
> Adrian,
>
> Yeah, that's what it said. :-(
>
>> ps ax | grep postgres
>
> Only the earlier version:
>
> # ps ax | grep postgres
> 1822 ? S 0:33 postgres -D /var/lib/pgsql/9.3.4/data/
> 1824 ? Ss 0:01 postgres: checkpointer process
> 1825 ? Ss 0:21 postgres: writer process
> 1826 ? Ss 0:21 postgres: wal writer process
> 1827 ? Ss 0:45 postgres: autovacuum launcher process
> 1828 ? Ss 1:10 postgres: stats collector process
>
> I have not run init on 9.4.5 yet. Thought I should learn how best to
> proceed first.

A question are you really using 9.4.5 or 9.5.4(the latest version of 9.5)?

And if you want to use 9.4 I would say use the latest(9.4.9).

If the above is a yes, any particular reason you do not want to move to 9.5?

>
>> Before you do any of that you need to verify that the servers you want
>> are running. Remember they can not share a port, so you need to look
>> at what the port settings are in the respective postgresql.conf files.
>
> In /var/lib/pgsql/9.3.4/data/postgresql.conf the port assignment
> (5432) is
> commented out, yet that's the port I've always seen assigned.

That is the default port which is why it is shown and is commented out.
If you want to run on a different port you can uncomment it and enter a
different port number say 5442.

>
> What do you suggest as the procedure for me to follow to clean this all
> up?

Get your 9.4(5) database instance up and running. I generally keep the
existing database on the default port(5432) and use a greater port
number for Postgres versions greater then my current version, so in your
case say 5442 for the new instance.

Once you verify that the new instance is running and you can connect to
it then:

psql -U some_user -d postgres -p 5442 -f your_dumpall.sql

In the above some_user has to have sufficient database privileges to
deal with all the objects in the your_dumpall.sql. For instance if you
are using plpythonu then it needs superuser privileges to install.

>
> TIA,
>
> Rich
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com