Thread: Move cluster to new host, upgraded version

Move cluster to new host, upgraded version

From
Rich Shepard
Date:
   My current desktop server/workstation is running version 10.5. I'm
configuring a replacement desktop and have installed version 11.1 on it. To
copy all databases from the 10.5 version to the 11.1 version I assume that I
should do a pg_dumpall on the current host and read in that file on the
replacement host. Is this the proper procedure?

TIA,

Rich


Re: Move cluster to new host, upgraded version

From
Andrew Gierth
Date:
>>>>> "Rich" == Rich Shepard <rshepard@appl-ecosys.com> writes:

 Rich> My current desktop server/workstation is running version 10.5.
 Rich> I'm configuring a replacement desktop and have installed version
 Rich> 11.1 on it. To copy all databases from the 10.5 version to the
 Rich> 11.1 version I assume that I should do a pg_dumpall on the
 Rich> current host and read in that file on the replacement host. Is
 Rich> this the proper procedure?

The most reliable and preferred procedure is to use the _new_ version's
pg_dumpall, for example by allowing access to the old host from the new
one (possibly using an ssh port forward), or (on OSes that make it easy
to do package installs of multiple versions) to install the new pg_dump
and pg_dumpall on the old system.

Using the old version's pg_dumpall also generally speaking works, but
there may be occasional rough edges.

-- 
Andrew (irc:RhodiumToad)


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/11/18 11:21 AM, Rich Shepard wrote:
>    My current desktop server/workstation is running version 10.5. I'm
> configuring a replacement desktop and have installed version 11.1 on it. To
> copy all databases from the 10.5 version to the 11.1 version I assume 
> that I
> should do a pg_dumpall on the current host and read in that file on the
> replacement host. Is this the proper procedure?

See Andrew's reply for pg_dumpall advice.

Not sure if you have any extensions or not, but the part I often skip is 
installing extensions in the new cluster before running the dump restore.

> 
> TIA,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Sun, 11 Nov 2018, Adrian Klaver wrote:

> Not sure if you have any extensions or not, but the part I often skip is
> installing extensions in the new cluster before running the dump restore.

   Thanks, Adrian. No extensions here.

Regards,

Rich


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Sun, 11 Nov 2018, Andrew Gierth wrote:

> The most reliable and preferred procedure is to use the _new_ version's
> pg_dumpall, for example by allowing access to the old host from the new
> one (possibly using an ssh port forward), or (on OSes that make it easy to
> do package installs of multiple versions) to install the new pg_dump and
> pg_dumpall on the old system.

Andrew,

   Using the SlackBuilds.org script with an updated version number worked for
11.1. So, I'll install it on the old desktop and upgrade in place. Then I
can use the 11.1 pg_dumpall on the data directory and copy that over to the
new desktop.

Thanks,

Rich


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/11/18 12:18 PM, Rich Shepard wrote:
> On Sun, 11 Nov 2018, Andrew Gierth wrote:
> 
>> The most reliable and preferred procedure is to use the _new_ version's
>> pg_dumpall, for example by allowing access to the old host from the new
>> one (possibly using an ssh port forward), or (on OSes that make it 
>> easy to
>> do package installs of multiple versions) to install the new pg_dump and
>> pg_dumpall on the old system.
> 
> Andrew,
> 
>    Using the SlackBuilds.org script with an updated version number 
> worked for
> 11.1. So, I'll install it on the old desktop and upgrade in place. Then I
> can use the 11.1 pg_dumpall on the data directory and copy that over to the
> new desktop.

pg_dumpall is going to need to run against a Postgres server not just a 
data directory. If both your old and new machines are on the same 
network, why not just point the 11.1 pg_dumpall(on the new machine) at 
the 9.5 server running on the old machine?

> 
> Thanks,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Sun, 11 Nov 2018, Adrian Klaver wrote:

> pg_dumpall is going to need to run against a Postgres server not just a data 
> directory.

Adrian,

   Of course. Yet it's the data directory that's written to the .sql file.

> If both your old and new machines are on the same network, why not just
> point the 11.1 pg_dumpall(on the new machine) at the 9.5 server running on
> the old machine?

   The old host is running 10.5. Haven't tried to run an application on one
host using data on another host. I'll look at which tool will do that.

Thanks,

Rich


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Sun, 11 Nov 2018, Rich Shepard wrote:

> Haven't tried to run an application on one host using data on another
> host. I'll look at which tool will do that.

   Looks like the pg_dumpall '-h' option will act on the other host's data
directory.

Regards,

Rich


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/11/18 12:51 PM, Rich Shepard wrote:
> On Sun, 11 Nov 2018, Adrian Klaver wrote:
> 
>> pg_dumpall is going to need to run against a Postgres server not just 
>> a data directory.
> 
> Adrian,
> 
>    Of course. Yet it's the data directory that's written to the .sql file.

In order for pg_dumpall to access the data directory the Postgres server 
has to be running. In your previous post you said you where going to 
upgrade in place, not sure what that means. That seemed to be overkill 
and given your posts from a week(?) or so ago, possibly a problem.

> 
>> If both your old and new machines are on the same network, why not just
>> point the 11.1 pg_dumpall(on the new machine) at the 9.5 server 
>> running on
>> the old machine?
> 
>    The old host is running 10.5. Haven't tried to run an application on one
> host using data on another host. I'll look at which tool will do that.

pg_dump(all) are clients designed to do just that. I and many others do 
it all the time. Supply the proper host information and you should be 
golden.

> 
> Thanks,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/11/18 12:53 PM, Rich Shepard wrote:
> On Sun, 11 Nov 2018, Rich Shepard wrote:
> 
>> Haven't tried to run an application on one host using data on another
>> host. I'll look at which tool will do that.
> 
>    Looks like the pg_dumpall '-h' option will act on the other host's data
> directory.

No it will work on the other hosts's Postgres server which in turn will 
pull from it's data directory.

> 
> Regards,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Ron
Date:
On 11/11/2018 02:51 PM, Rich Shepard wrote:
> On Sun, 11 Nov 2018, Adrian Klaver wrote:
>
>> pg_dumpall is going to need to run against a Postgres server not just a 
>> data directory.
>
> Adrian,
>
>   Of course. Yet it's the data directory that's written to the .sql file.

Unless your db is small, do a parallel dump.  Even then, do a "-Fc" backup 
instead.  That's been the recommended method for many years.


-- 
Angular momentum makes the world go 'round.


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Sun, 11 Nov 2018, Ron wrote:

> Unless your db is small, do a parallel dump. Even then, do a "-Fc" backup
> instead. That's been the recommended method for many years.

Ron,

   I've several databases, none 'large.' When I've used pg_dumpall in the
past it's always with the clean option (-c) and the output directed to a
specified filename with the -f option. I don't find an -F option on the man
page.

   Anywho, specifying the 10.5 version's hostname (-h) to pg_dumpall on the
11.1 version resulted a few seconds later with an output file.

Regards,

Rich


Re: Move cluster to new host, upgraded version

From
Ron
Date:
On 11/12/2018 07:55 AM, Rich Shepard wrote:
> On Sun, 11 Nov 2018, Ron wrote:
>
>> Unless your db is small, do a parallel dump. Even then, do a "-Fc" backup
>> instead. That's been the recommended method for many years.
>
> Ron,
>
>   I've several databases, none 'large.' When I've used pg_dumpall in the
> past it's always with the clean option (-c) and the output directed to a
> specified filename with the -f option. I don't find an -F option on the man
> page.

Hmm.  Apparently -F is only an option to pg_dump.  (I use multiple pg_dump 
statements + "pg_dumpall --globals-only".)

-- 
Angular momentum makes the world go 'round.


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Sun, 11 Nov 2018, Rich Shepard wrote:

> Looks like the pg_dumpall '-h' option will act on the other host's data
> directory.

   Worked as advertised. Just read the dumped file into the new 11.1 data
directory.

   The new installation is asking for my password to access my databases.
Where do I turn this off?

Rich


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/13/18 3:47 PM, Rich Shepard wrote:
> On Sun, 11 Nov 2018, Rich Shepard wrote:
> 
>> Looks like the pg_dumpall '-h' option will act on the other host's data
>> directory.
> 
>    Worked as advertised. Just read the dumped file into the new 11.1 data
> directory.
> 
>    The new installation is asking for my password to access my databases.
> Where do I turn this off?

You have two options:

1) The preferred one. Keep the password and create a .pgpass file to 
hold the password:

https://www.postgresql.org/docs/10/libpq-pgpass.html

My guess is you had one on the other machine.

2) Change your auth method in pg_hba.conf:

https://www.postgresql.org/docs/10/client-authentication.html
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Tue, 13 Nov 2018, Adrian Klaver wrote:

> You have two options:
>
> 1) The preferred one. Keep the password and create a .pgpass file to hold the 
> password:
> https://www.postgresql.org/docs/10/libpq-pgpass.html

Adrian,

   That's database-specific if I read the manual page correctly.

> My guess is you had one on the other machine.

   Nope. I've been running postgres since 1997 and never used a password
since I'm the only one using the databases.

> 2) Change your auth method in pg_hba.conf:
> https://www.postgresql.org/docs/10/client-authentication.html

   /var/lib/pgsql/11/data/pg_hba.conf has trust as the local authentication
method for all databases. Perhaps I need to restart the server after loading
the databases. Will try that.

Thanks,

Rich


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/13/18 4:24 PM, Rich Shepard wrote:
> On Tue, 13 Nov 2018, Adrian Klaver wrote:
> 
>> You have two options:
>>
>> 1) The preferred one. Keep the password and create a .pgpass file to 
>> hold the password:
>> https://www.postgresql.org/docs/10/libpq-pgpass.html
> 
> Adrian,
> 
>    That's database-specific if I read the manual page correctly.

No:

" Each of the first four fields can be a literal value, or *, which 
matches anything. "

> 
>> My guess is you had one on the other machine.
> 
>    Nope. I've been running postgres since 1997 and never used a password
> since I'm the only one using the databases.
> 
>> 2) Change your auth method in pg_hba.conf:
>> https://www.postgresql.org/docs/10/client-authentication.html
> 
>    /var/lib/pgsql/11/data/pg_hba.conf has trust as the local authentication

If the record starts with local then that is for socket connections.

If you are connecting to a host e.g -h localhost then you need to look 
at the host records.

> method for all databases. Perhaps I need to restart the server after 
> loading
> the databases. Will try that.
> 
> Thanks,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Tue, 13 Nov 2018, Adrian Klaver wrote:

> No:
> " Each of the first four fields can be a literal value, or *, which matches 
> anything. "

Adrian,

   Okay.

> If the record starts with local then that is for socket connections.
> If you are connecting to a host e.g -h localhost then you need to look at the 
> host records.

   I'm connecting from the same host on which the server is installed. I've
always used 'psql <database_name>' and been connected. The new desktop's
pg_hba.conf is identical to the old desktop's pg_hba.conf, but only the new
one is now asking for a password. Permissions on both are the same.

Thanks,

Rich


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/13/18 4:41 PM, Rich Shepard wrote:
> On Tue, 13 Nov 2018, Adrian Klaver wrote:
> 
>> No:
>> " Each of the first four fields can be a literal value, or *, which 
>> matches anything. "
> 
> Adrian,
> 
>    Okay.
> 
>> If the record starts with local then that is for socket connections.
>> If you are connecting to a host e.g -h localhost then you need to look 
>> at the host records.
> 
>    I'm connecting from the same host on which the server is installed. I've
> always used 'psql <database_name>' and been connected. The new desktop's

Please show complete connection command.

> pg_hba.conf is identical to the old desktop's pg_hba.conf, but only the new
> one is now asking for a password. Permissions on both are the same.

Can you show us the new pg_hba.conf?

> 
> Thanks,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/13/18 3:47 PM, Rich Shepard wrote:
> On Sun, 11 Nov 2018, Rich Shepard wrote:
> 
>> Looks like the pg_dumpall '-h' option will act on the other host's data
>> directory.
> 
>    Worked as advertised. Just read the dumped file into the new 11.1 data
> directory.

Just realized the question I should have asked is:

How did you get the pg_dumpall file processed by Postgres?

In other words how did you do it without a password?

> 
>    The new installation is asking for my password to access my databases.
> Where do I turn this off?
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Tue, 13 Nov 2018, Adrian Klaver wrote:

> Just realized the question I should have asked is:
> How did you get the pg_dumpall file processed by Postgres?
> In other words how did you do it without a password?

   As user postgres I entered the command
$ psql -f dump-all.sql

   In any case, I need to back up because I missed something when
initializing the cluster.

   ps ax | grep postgres

shows a number of processes, but psql tells me there's no server running,
and there is no postmaster.opts or postmaster.pid in the data directory.

   I can delete contents of the data directory and re-initdb, or re-install
the application and start from scratch.

Advice appreciated,

Rich


Re: Move cluster to new host, upgraded version

From
Adrian Klaver
Date:
On 11/14/18 6:58 AM, Rich Shepard wrote:
> On Tue, 13 Nov 2018, Adrian Klaver wrote:
> 
>> Just realized the question I should have asked is:
>> How did you get the pg_dumpall file processed by Postgres?
>> In other words how did you do it without a password?
> 
>    As user postgres I entered the command
> $ psql -f dump-all.sql
> 
>    In any case, I need to back up because I missed something when
> initializing the cluster.
> 
>    ps ax | grep postgres
> 
> shows a number of processes, but psql tells me there's no server running,
> and there is no postmaster.opts or postmaster.pid in the data directory.
> 
>    I can delete contents of the data directory and re-initdb, or re-install
> the application and start from scratch.

It was running when you did this:

psql -f dump-all.sql

correct?

Seems to me it is a start up script issue.

Have you rebooted the computer since the last time Postgres ran?

Is there a startup script in init.d/ or where ever your scripts are?

Can you start the server manually using pg_ctl?

> 
> Advice appreciated,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: Move cluster to new host, upgraded version

From
Tom Lane
Date:
Rich Shepard <rshepard@appl-ecosys.com> writes:
>    ps ax | grep postgres
> shows a number of processes, but psql tells me there's no server running,
> and there is no postmaster.opts or postmaster.pid in the data directory.

Perhaps those are associated with some other data directory?

You could try using lsof on one of them to see what its current working
directory is (or on Linux, examine /proc/NN/cwd).  lsof on whichever
one(s) is/are postmasters would also tell you what sockets they're
listening on.

            regards, tom lane


Re: Move cluster to new host, upgraded version

From
Rich Shepard
Date:
On Wed, 14 Nov 2018, Tom Lane wrote:

> Perhaps those are associated with some other data directory?

Tom/Adrian,

   I think that I FUBARed the upgrade from 10.3 to 11.1. I've removed both
packages and am rebuilding 11.1. I'll install it, run initdb on it, then
re-run pg_dumpall on the 10.5 data directory on the old desktop. Starting
from scratch on this new desktop will save time for all of us.

Thanks,

Rich


Re: Move cluster to new host, upgraded version [DONE]

From
Rich Shepard
Date:
On Wed, 14 Nov 2018, Rich Shepard wrote:

> I think that I FUBARed the upgrade from 10.3 to 11.1. I've removed both
> packages and am rebuilding 11.1. I'll install it, run initdb on it, then
> re-run pg_dumpall on the 10.5 data directory on the old desktop. Starting
> from scratch on this new desktop will save time for all of us.

   All fixed now. To complete the thread this is what I did:

   1) Removed Slackware packages for postgresql-10.3 and -11.1.
   2) Removed /var/lib/pgsql/10.3 and 11.1.
   3) Re-built postgresql-11.1 and re-installed it.
   4) As user 'postgres' ran initdb and pg_ctl start pointing to
     /var/lib/pgsql/11/data.
   5) Edited postgresql.conf and pg_hba.conf to accept connections from all
     hosts on the LAN.
   6) Re-started postgres.
   7) As 'postgre' ran
     pg_dumpall -h salmo -c -f pg-all-2018-11-14.sql
   8) Then postgres ran
     psql -f pg-all-2018-11-14.sql

   Now I, as a user, can access my databases without a password.

   Thank you, Adrian, for your patient help. And you, Tom, for your helpful
comment.

Best regards,

Rich