Move cluster to new host, upgraded version

"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)

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

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

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

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

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

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

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

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

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.

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

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.

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

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

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

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

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

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

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