Thread: Move cluster to new host, upgraded version
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
>>>>> "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
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
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
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
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
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