Re: [GENERAL] Running pg_upgrade under Debian
On 4/20/15 6:09 PM, Bruce Momjian wrote: On Mon, Apr 20, 2015 at 07:06:37PM -0300, Alvaro Herrera wrote: ISTM there's a documentation bug here: in the code, the dump method checks for tablespaces and raises an error if they are found, but the upgrade method does not check. I think the documentation should state that only the dump method does not support tablespaces. OK, it would be nice if someone could report that to upstream Debian. I am a little confused why pg_dump/pg_restore can't use tablespaces though. Years ago we used to not use PG-major-version-specific subdirectories in tablespaces, but we added that for pg_upgrade, and I am sure they would work fine for pg_dump too. https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=772202 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
Re: Peter Eisentraut 2015-04-24 553a3b85.1070...@gmx.net On 4/20/15 6:09 PM, Bruce Momjian wrote: On Mon, Apr 20, 2015 at 07:06:37PM -0300, Alvaro Herrera wrote: ISTM there's a documentation bug here: in the code, the dump method checks for tablespaces and raises an error if they are found, but the upgrade method does not check. I think the documentation should state that only the dump method does not support tablespaces. OK, it would be nice if someone could report that to upstream Debian. I am a little confused why pg_dump/pg_restore can't use tablespaces though. Years ago we used to not use PG-major-version-specific subdirectories in tablespaces, but we added that for pg_upgrade, and I am sure they would work fine for pg_dump too. https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=772202 Yeah I know, it's on the extended TODO list... Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
Re: [GENERAL] Running pg_upgrade under Debian
On 04/20/2015 02:42 PM, Bruce Momjian wrote: On Mon, Apr 20, 2015 at 02:41:09PM -0700, Adrian Klaver wrote: On 04/20/2015 12:49 PM, Bruce Momjian wrote: On Sat, Apr 18, 2015 at 09:08:20AM +1000, rob stone wrote: For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten Indeed I have that program installed in /usr/lib but the man pages state that it cannot handle tablespaces. Wow, that is odd. I wonder why. Best guess is because pg_upgradecluster is a wrapper script that by default uses pg_dump/pg_restore. Using is pg_upgrade is the second choice. http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html But pg_upgrade supports tablespaces, and I assume pg_dump/pg_restore do as well. I don't think it is about the underlying programs, it is about teaching the wrapper script what do with the choices. Sort of like pgAdmin not supporting all pg_backup/pg_restore combinations or for that matter pg_restore not knowing what to do with a plain text pg_dump file. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On Mon, Apr 20, 2015 at 03:02:48PM -0700, Adrian Klaver wrote: But pg_upgrade supports tablespaces, and I assume pg_dump/pg_restore do as well. I don't think it is about the underlying programs, it is about teaching the wrapper script what do with the choices. Sort of like pgAdmin not supporting all pg_backup/pg_restore combinations or for that matter pg_restore not knowing what to do with a plain text pg_dump file. Understoo, but I was not aware there was anything special required for pg_upgrade to support tablespaces. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
ISTM there's a documentation bug here: in the code, the dump method checks for tablespaces and raises an error if they are found, but the upgrade method does not check. I think the documentation should state that only the dump method does not support tablespaces. Bruce Momjian wrote: On Mon, Apr 20, 2015 at 02:41:09PM -0700, Adrian Klaver wrote: On 04/20/2015 12:49 PM, Bruce Momjian wrote: On Sat, Apr 18, 2015 at 09:08:20AM +1000, rob stone wrote: For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten Indeed I have that program installed in /usr/lib but the man pages state that it cannot handle tablespaces. Wow, that is odd. I wonder why. Best guess is because pg_upgradecluster is a wrapper script that by default uses pg_dump/pg_restore. Using is pg_upgrade is the second choice. http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html But pg_upgrade supports tablespaces, and I assume pg_dump/pg_restore do as well. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On Mon, Apr 20, 2015 at 07:06:37PM -0300, Alvaro Herrera wrote: ISTM there's a documentation bug here: in the code, the dump method checks for tablespaces and raises an error if they are found, but the upgrade method does not check. I think the documentation should state that only the dump method does not support tablespaces. OK, it would be nice if someone could report that to upstream Debian. I am a little confused why pg_dump/pg_restore can't use tablespaces though. Years ago we used to not use PG-major-version-specific subdirectories in tablespaces, but we added that for pg_upgrade, and I am sure they would work fine for pg_dump too. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On 04/20/2015 03:18 PM, Alvaro Herrera wrote: Bruce Momjian wrote: On Mon, Apr 20, 2015 at 07:06:37PM -0300, Alvaro Herrera wrote: ISTM there's a documentation bug here: in the code, the dump method checks for tablespaces and raises an error if they are found, but the upgrade method does not check. I think the documentation should state that only the dump method does not support tablespaces. OK, it would be nice if someone could report that to upstream Debian. That's why I added m...@debian.org to the CC list. I am a little confused why pg_dump/pg_restore can't use tablespaces though. Years ago we used to not use PG-major-version-specific subdirectories in tablespaces, but we added that for pg_upgrade, and I am sure they would work fine for pg_dump too. Maybe they haven't gotten the memo yet. This was implemented in 9.0, and as I recall Debian supported 8.4 until recently. Perhaps they just need to remove the check now. Well in the code I see a checks for = 8.2 stepping up to 9.4, so I not sure they could just remove it. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On Mon, Apr 20, 2015 at 02:41:09PM -0700, Adrian Klaver wrote: On 04/20/2015 12:49 PM, Bruce Momjian wrote: On Sat, Apr 18, 2015 at 09:08:20AM +1000, rob stone wrote: For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten Indeed I have that program installed in /usr/lib but the man pages state that it cannot handle tablespaces. Wow, that is odd. I wonder why. Best guess is because pg_upgradecluster is a wrapper script that by default uses pg_dump/pg_restore. Using is pg_upgrade is the second choice. http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html But pg_upgrade supports tablespaces, and I assume pg_dump/pg_restore do as well. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
Bruce Momjian wrote: On Mon, Apr 20, 2015 at 07:06:37PM -0300, Alvaro Herrera wrote: ISTM there's a documentation bug here: in the code, the dump method checks for tablespaces and raises an error if they are found, but the upgrade method does not check. I think the documentation should state that only the dump method does not support tablespaces. OK, it would be nice if someone could report that to upstream Debian. That's why I added m...@debian.org to the CC list. I am a little confused why pg_dump/pg_restore can't use tablespaces though. Years ago we used to not use PG-major-version-specific subdirectories in tablespaces, but we added that for pg_upgrade, and I am sure they would work fine for pg_dump too. Maybe they haven't gotten the memo yet. This was implemented in 9.0, and as I recall Debian supported 8.4 until recently. Perhaps they just need to remove the check now. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On 04/20/2015 12:49 PM, Bruce Momjian wrote: On Sat, Apr 18, 2015 at 09:08:20AM +1000, rob stone wrote: For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten Indeed I have that program installed in /usr/lib but the man pages state that it cannot handle tablespaces. Wow, that is odd. I wonder why. Best guess is because pg_upgradecluster is a wrapper script that by default uses pg_dump/pg_restore. Using is pg_upgrade is the second choice. http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html -- Adrian Klaver adrian.kla...@aklaver.comp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On Mon, 2015-04-20 at 18:05 -0400, Bruce Momjian wrote: On Mon, Apr 20, 2015 at 03:02:48PM -0700, Adrian Klaver wrote: But pg_upgrade supports tablespaces, and I assume pg_dump/pg_restore do as well. I don't think it is about the underlying programs, it is about teaching the wrapper script what do with the choices. Sort of like pgAdmin not supporting all pg_backup/pg_restore combinations or for that matter pg_restore not knowing what to do with a plain text pg_dump file. Understoo, but I was not aware there was anything special required for pg_upgrade to support tablespaces. pg_dump will handle tablespaces when you create a sql style dump file. However, you have to alter one line where it sets default_tablespace to null. Obviously, when you run psql to read the dump file the tablespaces have to exist. There is no create statement as it won't know where you want to mount your tablespaces. I've used my Plan B. Ran pg_dump out of the 9.3 instance, altered that one line, stopped 9.3, started 9.4, ran psql as the postgres user, created the user and tablespaces and imported the dump file. It all worked as intended. So, I now have one of my test databases running under 9.4. Cheers, Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On Sat, Apr 18, 2015 at 09:08:20AM +1000, rob stone wrote: For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten Indeed I have that program installed in /usr/lib but the man pages state that it cannot handle tablespaces. Wow, that is odd. I wonder why. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On Sun, Apr 19, 2015 at 9:34 AM, rob stone floripa...@gmail.com wrote: On Fri, 2015-04-17 at 16:16 -0700, Adrian Klaver wrote: On 04/17/2015 03:09 PM, rob stone wrote: Hello, I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this error:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade ... So, on the command line I'm putting listen_addresses=localhost which pg_upgrade picks up but the following option sets this to ''. It is starting pg_ctl in the 9.3/bin directory but makes the socket directory in 9.4/bin?? Note that 9.4/bin seems to be the current working directory from which you are running the command. That is probably not such a good idea. If pg_upgrade is actually reading the conf files it should not be necessary to supply any of these options, as port numbers, PID file locations, etc. are specified there. If pg_upgrade used the values from the config files, then anyone who is accustomed to connecting to the database with those setting could unthinkingly connect to it during the upgrade process and screw up the upgrade. It intentionally chooses methods to prevent that from happening, by putting the socket somewhere private, or at least unpredictable. waiting for server to start2596 5533cb8c.a24 2015-04-20 01:36:44 AESTLOG: redirecting log output to logging collector process 2596 5533cb8c.a24 2015-04-20 01:36:44 AESTHINT: Future log output will appear in directory pg_log However, it appears appear that pg_upgrade does honor the logging-collector related settings from the config file (should it? I can see arguments both ways) and you so you have to look in the pg_log directory to figure out what the actual problem is. Cheers, Jeff
Re: [GENERAL] Running pg_upgrade under Debian
On Fri, 2015-04-17 at 16:16 -0700, Adrian Klaver wrote: On 04/17/2015 03:09 PM, rob stone wrote: Hello, I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this error:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /home/postgres/data93/userqueries -D /home/postgres/data94/userqueries -U pguserqueries check for /home/postgres/data93/userqueries/base failed: No such file or directory Failure, exiting postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade --version pg_upgrade (PostgreSQL) 9.4.1 I have two tablespaces defined for this data -- one to hold the tables and another for the indices. There is no base file or directory. Guessing /home/postgres/data*/userqueries/ is the location of your tablespace, not the cluster directory. Correct. The -d and -D need to point at the cluster directory, which will be something like : /var/lib/postgresql/9.3/main Do I just create a dummy directory named base? No, you need to point to the correct directory. See here for more information: http://www.postgresql.org/docs/9.4/interactive/pgupgrade.html Actually, the wiki pages on upgrading gave me some more information, particularly about specifying where to find postgresql.conf. I adopted the symlink suggestion. However, running the following still gives errors:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /var/lib/postgresql/9.3/main -D /var/lib/postgresql/9.4/main -p 5432 -P 5433 -o ' -c listen_addresses=localhost ' -O ' -c listen_addresses=localhost ' --check --verbose Running in verbose mode Performing Consistency Checks - Checking cluster versions ok pg_control values: First log segment after reset:00010002 pg_control version number:937 Catalog version number: 201306121 Database system identifier: 6026352641161422830 Latest checkpoint's TimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/684 Latest checkpoint's NextOID: 12036 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID:674 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0 Current pg_control values: pg_control version number:942 Catalog version number: 201409291 Database system identifier: 6108663141646003887 Latest checkpoint's TimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/719 Latest checkpoint's NextOID: 12142 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID:675 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Size of a large-object chunk: 2048 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0 Values to be changed: First log segment after reset:00010002 /usr/lib/postgresql/9.3/bin/pg_ctl -w -l pg_upgrade_server.log -D /var/lib/postgresql/9.3/main -o -p 5432 -b -c listen_addresses=localhost -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/usr/lib/postgresql/9.4/bin' start pg_upgrade_server.log 21 *failure* There were problems executing /usr/lib/postgresql/9.3/bin/pg_ctl -w -l pg_upgrade_server.log -D /var/lib/postgresql/9.3/main -o -p 5432 -b -c listen_addresses=localhost -c listen_addresses='' -c unix_socket_permissions=0700 -c
Re: [GENERAL] Running pg_upgrade under Debian
On Sat, 2015-04-18 at 00:25 +0200, Karsten Hilbert wrote: On Sat, Apr 18, 2015 at 08:09:43AM +1000, rob stone wrote: I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this error:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /home/postgres/data93/userqueries -D /home/postgres/data94/userqueries -U pguserqueries For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten Indeed I have that program installed in /usr/lib but the man pages state that it cannot handle tablespaces. So, I guess I have to move to Plan B, use pg_dump from the old version and then import into 9.4.1. Thanks for your help. Robert check for /home/postgres/data93/userqueries/base failed: No such file or directory Failure, exiting postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade --version pg_upgrade (PostgreSQL) 9.4.1 I have two tablespaces defined for this data -- one to hold the tables and another for the indices. There is no base file or directory. Do I just create a dummy directory named base? If there is supposed to be a directory or file named base why wasn't it created by initdb? Regards, Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On Sat, Apr 18, 2015 at 08:09:43AM +1000, rob stone wrote: I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this error:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /home/postgres/data93/userqueries -D /home/postgres/data94/userqueries -U pguserqueries For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten check for /home/postgres/data93/userqueries/base failed: No such file or directory Failure, exiting postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade --version pg_upgrade (PostgreSQL) 9.4.1 I have two tablespaces defined for this data -- one to hold the tables and another for the indices. There is no base file or directory. Do I just create a dummy directory named base? If there is supposed to be a directory or file named base why wasn't it created by initdb? Regards, Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On 04/17/2015 03:09 PM, rob stone wrote: Hello, I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this error:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /home/postgres/data93/userqueries -D /home/postgres/data94/userqueries -U pguserqueries check for /home/postgres/data93/userqueries/base failed: No such file or directory Failure, exiting postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade --version pg_upgrade (PostgreSQL) 9.4.1 I have two tablespaces defined for this data -- one to hold the tables and another for the indices. There is no base file or directory. Guessing /home/postgres/data*/userqueries/ is the location of your tablespace, not the cluster directory. The -d and -D need to point at the cluster directory, which will be something like : /var/lib/postgresql/9.3/main Do I just create a dummy directory named base? No, you need to point to the correct directory. See here for more information: http://www.postgresql.org/docs/9.4/interactive/pgupgrade.html Or use pg_upgradecluster: http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html If there is supposed to be a directory or file named base why wasn't it created by initdb? Regards, Robert -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
