Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian wrote: Well, having seen no replies, I am going to apply the version of the patch in a few days that keeps the old vacuum-disable behavior for older releases, and uses the -b flag for newer ones by testing the catalog version, e.g.: snprintf(cmd, sizeof(cmd), SYSTEMQUOTE \%s/pg_ctl\ -l \%s\ -D \%s\ -o \-p %d %s\ start \%s\ 21 SYSTEMQUOTE, bindir, output_filename, datadir, port, (cluster-controldata.cat_ver = BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? -b : -c autovacuum=off -c autovacuum_freeze_max_age=20, log_opts.filename); I know people like that pg_upgrade doesn't care much about what version it is running on, but it is really the ability of pg_upgrade to ignore changes made to the server that is really why pg_upgrade is useful, and this change makes pg_upgrade even more immune to such changes. Applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I thought some more about this and I don't want autovacuum to run on the old server. This is because pg_dumpall --binary-upgrade --schema-only grabs the datfrozenxid for all the databases at the start, then connects to each database to gets the relfrozenxids. I don't want to risk any advancement of either of those during the pg_dumpall run. Why? It doesn't really matter --- if you grab a value that is older than the latest, it's still valid. As Robert said, you're over-engineering this, and thereby introducing potential failure modes, for no gain. Uh, I am kind of paranoid about pg_upgrade because it is trying to do something Postgres was never designed to do. I am a little worried that we would be assuming that pg_dumpall always does the datfrozenxid first and if we ever did it last we would have relfrozenxids before the datfrozenxid. I am worried if we don't prevent autovacuum on the old server that pg_upgrade will be more fragile to changes in other parts of the system. Hold, I overstated the fragility issue above. I now realize that the old system is not going to change and that I only need to worry about future changes, where are handled by the new -b flag, so maybe we can get away with only stopping autovacuum on the new server, but I would need someone to verify that, and this would be a change in the way 9.0 pg_upgrade operated because it did disable autovacuum on the old and new servers with 99.9% reliability. Well, having seen no replies, I am going to apply the version of the patch in a few days that keeps the old vacuum-disable behavior for older releases, and uses the -b flag for newer ones by testing the catalog version, e.g.: snprintf(cmd, sizeof(cmd), SYSTEMQUOTE \%s/pg_ctl\ -l \%s\ -D \%s\ -o \-p %d %s\ start \%s\ 21 SYSTEMQUOTE, bindir, output_filename, datadir, port, (cluster-controldata.cat_ver = BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? -b : -c autovacuum=off -c autovacuum_freeze_max_age=20, log_opts.filename); I know people like that pg_upgrade doesn't care much about what version it is running on, but it is really the ability of pg_upgrade to ignore changes made to the server that is really why pg_upgrade is useful, and this change makes pg_upgrade even more immune to such changes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Robert Haas wrote: On Apr 21, 2011, at 6:22 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Huh? Why would that be? Seems like you've done something in the wrong place if that's an issue. Yeah, it is complicated. I don't really care if autovacuum runs on the old cluster (we only move the files while the server is down). We only want autovacuum not to mess with the relfrozenxids we set on the new cluster while the table file is empty. The other issue is that the old alpha binary will not know about the -b flag and hence will not start. Well, once again, why are you trying to do that? It's not the source postmaster that needs this flag. Well, consider that this also locks out non-super users so I figured it would be good to run the old and new in the same binary upgrade mode. Again, we can do just the new cluster for 9.1. I can also control the behavior based on the catalog version number, which seems the most logical. I think you are over-engineering this. Just use it for the new cluster only, full stop, and you'll be right as rain. I thought some more about this and I don't want autovacuum to run on the old server. This is because pg_dumpall --binary-upgrade --schema-only grabs the datfrozenxid for all the databases at the start, then connects to each database to gets the relfrozenxids. I don't want to risk any advancement of either of those during the pg_dumpall run. FYI, the existing code already doesn't allow autovacuum to run on the old or new cluster by setting autovacuum off and autovacuum_freeze_max_age very high, so this is not a behavior change --- just a more formalized way of turning off autovacuum. The attached patch uses catalog version to test; we use catalog version checking already for tablespace subdirectories. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c new file mode 100644 index d1dc5db..7623c5e *** a/contrib/pg_upgrade/check.c --- b/contrib/pg_upgrade/check.c *** check_cluster_compatibility(bool live_ch *** 264,270 /* Is it 9.0 but without tablespace directories? */ if (GET_MAJOR_VERSION(new_cluster.major_version) == 900 ! new_cluster.controldata.cat_ver TABLE_SPACE_SUBDIRS) pg_log(PG_FATAL, This utility can only upgrade to PostgreSQL version 9.0 after 2010-01-11\n because of backend API changes made during development.\n); } --- 264,270 /* Is it 9.0 but without tablespace directories? */ if (GET_MAJOR_VERSION(new_cluster.major_version) == 900 ! new_cluster.controldata.cat_ver CAT_VER_TABLE_SPACE_SUBDIRS) pg_log(PG_FATAL, This utility can only upgrade to PostgreSQL version 9.0 after 2010-01-11\n because of backend API changes made during development.\n); } diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h new file mode 100644 index 5ca570e..70d74ba *** a/contrib/pg_upgrade/pg_upgrade.h --- b/contrib/pg_upgrade/pg_upgrade.h *** *** 58,64 #define atooid(x) ((Oid) strtoul((x), NULL, 10)) /* OID system catalog preservation added during PG 9.0 development */ ! #define TABLE_SPACE_SUBDIRS 20100 /* * Each relation is represented by a relinfo structure. --- 58,66 #define atooid(x) ((Oid) strtoul((x), NULL, 10)) /* OID system catalog preservation added during PG 9.0 development */ ! #define CAT_VER_TABLE_SPACE_SUBDIRS 20100 ! /* postmaster/postgres -b (binary_upgrade) flag added during PG 9.1 development */ ! #define CAT_VER_BINARY_UPGRADE_SERVER_FLAG 201104221 /* * Each relation is represented by a relinfo structure. diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c new file mode 100644 index 2a0f50e..f7d7653 *** a/contrib/pg_upgrade/server.c --- b/contrib/pg_upgrade/server.c *** start_postmaster(ClusterInfo *cluster, b *** 173,178 --- 173,183 const char *datadir; unsigned short port; bool exit_hook_registered = false; + #ifndef WIN32 + char *output_filename = log_opts.filename; + #else + char *output_filename = DEVNULL; + #endif bindir = cluster-bindir; datadir = cluster-pgdata; *** start_postmaster(ClusterInfo *cluster, b *** 193,216 * same file because we get the error: The process cannot access the file * because it is being used by another process. so we have to send all * other output to 'nul'. ! * ! * Using autovacuum=off disables cleanup vacuum and analyze, but freeze ! * vacuums can still happen, so we set autovacuum_freeze_max_age to its ! * maximum. We assume all datfrozenxid and relfrozen values are less than ! * a gap of 20 from the current xid counter, so autovacuum will ! *
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian br...@momjian.us writes: I thought some more about this and I don't want autovacuum to run on the old server. This is because pg_dumpall --binary-upgrade --schema-only grabs the datfrozenxid for all the databases at the start, then connects to each database to gets the relfrozenxids. I don't want to risk any advancement of either of those during the pg_dumpall run. Why? It doesn't really matter --- if you grab a value that is older than the latest, it's still valid. As Robert said, you're over-engineering this, and thereby introducing potential failure modes, for no gain. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian wrote: Well, consider that this also locks out non-super users so I figured it would be good to run the old and new in the same binary upgrade mode. Again, we can do just the new cluster for 9.1. I can also control the behavior based on the catalog version number, which seems the most logical. I think you are over-engineering this. Just use it for the new cluster only, full stop, and you'll be right as rain. I thought some more about this and I don't want autovacuum to run on the old server. This is because pg_dumpall --binary-upgrade --schema-only grabs the datfrozenxid for all the databases at the start, then connects to each database to gets the relfrozenxids. I don't want to risk any advancement of either of those during the pg_dumpall run. FYI, the existing code already doesn't allow autovacuum to run on the old or new cluster by setting autovacuum off and autovacuum_freeze_max_age very high, so this is not a behavior change --- just a more formalized way of turning off autovacuum. The attached patch uses catalog version to test; we use catalog version checking already for tablespace subdirectories. OK, thinking even more, it seems I have to keep the old vacuum disable flag for pre-9.1 old clusters. This new patch does that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c new file mode 100644 index d1dc5db..7623c5e *** a/contrib/pg_upgrade/check.c --- b/contrib/pg_upgrade/check.c *** check_cluster_compatibility(bool live_ch *** 264,270 /* Is it 9.0 but without tablespace directories? */ if (GET_MAJOR_VERSION(new_cluster.major_version) == 900 ! new_cluster.controldata.cat_ver TABLE_SPACE_SUBDIRS) pg_log(PG_FATAL, This utility can only upgrade to PostgreSQL version 9.0 after 2010-01-11\n because of backend API changes made during development.\n); } --- 264,270 /* Is it 9.0 but without tablespace directories? */ if (GET_MAJOR_VERSION(new_cluster.major_version) == 900 ! new_cluster.controldata.cat_ver CAT_VER_TABLE_SPACE_SUBDIRS) pg_log(PG_FATAL, This utility can only upgrade to PostgreSQL version 9.0 after 2010-01-11\n because of backend API changes made during development.\n); } diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h new file mode 100644 index 5ca570e..70d74ba *** a/contrib/pg_upgrade/pg_upgrade.h --- b/contrib/pg_upgrade/pg_upgrade.h *** *** 58,64 #define atooid(x) ((Oid) strtoul((x), NULL, 10)) /* OID system catalog preservation added during PG 9.0 development */ ! #define TABLE_SPACE_SUBDIRS 20100 /* * Each relation is represented by a relinfo structure. --- 58,66 #define atooid(x) ((Oid) strtoul((x), NULL, 10)) /* OID system catalog preservation added during PG 9.0 development */ ! #define CAT_VER_TABLE_SPACE_SUBDIRS 20100 ! /* postmaster/postgres -b (binary_upgrade) flag added during PG 9.1 development */ ! #define CAT_VER_BINARY_UPGRADE_SERVER_FLAG 201104221 /* * Each relation is represented by a relinfo structure. diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c new file mode 100644 index 2a0f50e..ca4a957 *** a/contrib/pg_upgrade/server.c --- b/contrib/pg_upgrade/server.c *** start_postmaster(ClusterInfo *cluster, b *** 173,178 --- 173,183 const char *datadir; unsigned short port; bool exit_hook_registered = false; + #ifndef WIN32 + char *output_filename = log_opts.filename; + #else + char *output_filename = DEVNULL; + #endif bindir = cluster-bindir; datadir = cluster-pgdata; *** start_postmaster(ClusterInfo *cluster, b *** 193,199 * same file because we get the error: The process cannot access the file * because it is being used by another process. so we have to send all * other output to 'nul'. - * * Using autovacuum=off disables cleanup vacuum and analyze, but freeze * vacuums can still happen, so we set autovacuum_freeze_max_age to its * maximum. We assume all datfrozenxid and relfrozen values are less than --- 198,203 *** start_postmaster(ClusterInfo *cluster, b *** 202,216 */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE \%s/pg_ctl\ -l \%s\ -D \%s\ ! -o \-p %d -c autovacuum=off ! -c autovacuum_freeze_max_age=20\ ! start \%s\ 21 SYSTEMQUOTE, ! bindir, ! #ifndef WIN32 ! log_opts.filename, datadir, port, log_opts.filename); ! #else ! DEVNULL, datadir, port, DEVNULL); ! #endif exec_prog(true, %s, cmd); /* wait for the server to start properly */ --- 206,218 */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE \%s/pg_ctl\ -l \%s\ -D \%s\ ! -o \-p %d %s\ start \%s\ 21
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I thought some more about this and I don't want autovacuum to run on the old server. This is because pg_dumpall --binary-upgrade --schema-only grabs the datfrozenxid for all the databases at the start, then connects to each database to gets the relfrozenxids. I don't want to risk any advancement of either of those during the pg_dumpall run. Why? It doesn't really matter --- if you grab a value that is older than the latest, it's still valid. As Robert said, you're over-engineering this, and thereby introducing potential failure modes, for no gain. Uh, I am kind of paranoid about pg_upgrade because it is trying to do something Postgres was never designed to do. I am a little worried that we would be assuming that pg_dumpall always does the datfrozenxid first and if we ever did it last we would have relfrozenxids before the datfrozenxid. I am worried if we don't prevent autovacuum on the old server that pg_upgrade will be more fragile to changes in other parts of the system. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
On Fri, 2011-04-22 at 17:34 -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I thought some more about this and I don't want autovacuum to run on the old server. This is because pg_dumpall --binary-upgrade --schema-only grabs the datfrozenxid for all the databases at the start, then connects to each database to gets the relfrozenxids. I don't want to risk any advancement of either of those during the pg_dumpall run. Why? It doesn't really matter --- if you grab a value that is older than the latest, it's still valid. As Robert said, you're over-engineering this, and thereby introducing potential failure modes, for no gain. Uh, I am kind of paranoid about pg_upgrade because it is trying to do something Postgres was never designed to do. I am a little worried that we would be assuming that pg_dumpall always does the datfrozenxid first and if we ever did it last we would have relfrozenxids before the datfrozenxid. I am worried if we don't prevent autovacuum on the old server that pg_upgrade will be more fragile to changes in other parts of the system. If we back-patch the -b to 8.3, then we can always use it on both the old and new systems. Upgrading to the latest patch-level on both old and new should be a prerequisite for pg_upgrade anyway. That would turn the catalog check from a special case (use -b sometimes, other times don't; which could cause fragility and bugs), into just another sanity check with an easy workaround (your postgres doesn't support '-b', upgrade to the latest patch-level before upgrading). One of the things I like about the design of pg_upgrade is that it doesn't seem to have a lot of special cases for different version combinations. What do you think? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Jeff Davis wrote: On Fri, 2011-04-22 at 17:34 -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I thought some more about this and I don't want autovacuum to run on the old server. This is because pg_dumpall --binary-upgrade --schema-only grabs the datfrozenxid for all the databases at the start, then connects to each database to gets the relfrozenxids. I don't want to risk any advancement of either of those during the pg_dumpall run. Why? It doesn't really matter --- if you grab a value that is older than the latest, it's still valid. As Robert said, you're over-engineering this, and thereby introducing potential failure modes, for no gain. Uh, I am kind of paranoid about pg_upgrade because it is trying to do something Postgres was never designed to do. I am a little worried that we would be assuming that pg_dumpall always does the datfrozenxid first and if we ever did it last we would have relfrozenxids before the datfrozenxid. I am worried if we don't prevent autovacuum on the old server that pg_upgrade will be more fragile to changes in other parts of the system. If we back-patch the -b to 8.3, then we can always use it on both the old and new systems. Upgrading to the latest patch-level on both old and new should be a prerequisite for pg_upgrade anyway. That would turn the catalog check from a special case (use -b sometimes, other times don't; which could cause fragility and bugs), into just another sanity check with an easy workaround (your postgres doesn't support '-b', upgrade to the latest patch-level before upgrading). One of the things I like about the design of pg_upgrade is that it doesn't seem to have a lot of special cases for different version combinations. What do you think? Well, I am concerned that there isn't enough testing of the -b flag to be sure it has zero effect on a running server that is not doing a binary upgrade, which is why I liked doing it only in 9.1. And we would still need code to check if the -b flag is supported. We can save this for 9.2 if people prefer, but we would still need a PG version check, rather than a catalog version check. Of course, if people prefer backpatch, we can do that, but I would need many more eyes on this patch. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I thought some more about this and I don't want autovacuum to run on the old server. This is because pg_dumpall --binary-upgrade --schema-only grabs the datfrozenxid for all the databases at the start, then connects to each database to gets the relfrozenxids. I don't want to risk any advancement of either of those during the pg_dumpall run. Why? It doesn't really matter --- if you grab a value that is older than the latest, it's still valid. As Robert said, you're over-engineering this, and thereby introducing potential failure modes, for no gain. Uh, I am kind of paranoid about pg_upgrade because it is trying to do something Postgres was never designed to do. I am a little worried that we would be assuming that pg_dumpall always does the datfrozenxid first and if we ever did it last we would have relfrozenxids before the datfrozenxid. I am worried if we don't prevent autovacuum on the old server that pg_upgrade will be more fragile to changes in other parts of the system. Hold, I overstated the fragility issue above. I now realize that the old system is not going to change and that I only need to worry about future changes, where are handled by the new -b flag, so maybe we can get away with only stopping autovacuum on the new server, but I would need someone to verify that, and this would be a change in the way 9.0 pg_upgrade operated because it did disable autovacuum on the old and new servers with 99.9% reliability. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian wrote: Robert Haas wrote: On Thu, Mar 31, 2011 at 12:11 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Thu, Mar 31, 2011 at 11:32 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: ?I think the maintenance overhead of an invisible variable is too much. A simple GUC or command-line switch isn't much code. I like the idea of a command-line switch. If you want to do that you should gereralize it as --binary-upgrade in case we have other needs for it. Yeah. Or we could do a binary_upgrade GUC which has the effect of forcibly suppressing autovacuum, and maybe other things later. I think that's a lot less hazardous than fiddling with the autovacuum GUC. I like the idea of a command-line flag because it forces everything to be affected, and cannot be turned on and off in sessions --- if you are doing a binary upgrade, locked-down is good. :-) The attached patch adds a new postmaster/postgres binary upgrade mode (-b) which disables autovacuum, allows only super-user connections, and prevents pg_upgrade_support oid assignment when not in upgrade mode. It also modifies pg_upgrade to use this new mode rather than play with trying to stop autovacuum. This does fix a very rare bug that could happen if autovacuum_freeze_max_age were set to maximum by the user. I think this should be applied to PG 9.1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c new file mode 100644 index 2a0f50e..df2f2db *** a/contrib/pg_upgrade/server.c --- b/contrib/pg_upgrade/server.c *** start_postmaster(ClusterInfo *cluster, b *** 173,178 --- 173,183 const char *datadir; unsigned short port; bool exit_hook_registered = false; + #ifndef WIN32 + char *output_filename = log_opts.filename; + #else + char *output_filename = DEVNULL; + #endif bindir = cluster-bindir; datadir = cluster-pgdata; *** start_postmaster(ClusterInfo *cluster, b *** 193,216 * same file because we get the error: The process cannot access the file * because it is being used by another process. so we have to send all * other output to 'nul'. ! * ! * Using autovacuum=off disables cleanup vacuum and analyze, but freeze ! * vacuums can still happen, so we set autovacuum_freeze_max_age to its ! * maximum. We assume all datfrozenxid and relfrozen values are less than ! * a gap of 20 from the current xid counter, so autovacuum will ! * not touch them. */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE \%s/pg_ctl\ -l \%s\ -D \%s\ ! -o \-p %d -c autovacuum=off ! -c autovacuum_freeze_max_age=20\ ! start \%s\ 21 SYSTEMQUOTE, ! bindir, ! #ifndef WIN32 ! log_opts.filename, datadir, port, log_opts.filename); ! #else ! DEVNULL, datadir, port, DEVNULL); ! #endif exec_prog(true, %s, cmd); /* wait for the server to start properly */ --- 198,212 * same file because we get the error: The process cannot access the file * because it is being used by another process. so we have to send all * other output to 'nul'. ! * Use binary upgrade mode on the server (-b), if supported. */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE \%s/pg_ctl\ -l \%s\ -D \%s\ ! -o \-p %d %s\ start \%s\ 21 SYSTEMQUOTE, ! bindir, output_filename, datadir, port, ! (GET_MAJOR_VERSION(cluster-major_version) = 901) ? -b : , ! log_opts.filename); ! exec_prog(true, %s, cmd); /* wait for the server to start properly */ diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c new file mode 100644 index 8c5670f..870ddba *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *** heap_create_with_catalog(const char *rel *** 1051,1057 * Use binary-upgrade override for pg_class.oid/relfilenode, if * supplied. */ ! if (OidIsValid(binary_upgrade_next_heap_pg_class_oid) (relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE || relkind == RELKIND_VIEW || relkind == RELKIND_COMPOSITE_TYPE || relkind == RELKIND_FOREIGN_TABLE)) --- 1051,1058 * Use binary-upgrade override for pg_class.oid/relfilenode, if * supplied. */ ! if (IsBinaryUpgrade ! OidIsValid(binary_upgrade_next_heap_pg_class_oid) (relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE || relkind == RELKIND_VIEW || relkind == RELKIND_COMPOSITE_TYPE || relkind == RELKIND_FOREIGN_TABLE)) *** heap_create_with_catalog(const char *rel *** 1059,1065 relid = binary_upgrade_next_heap_pg_class_oid; binary_upgrade_next_heap_pg_class_oid = InvalidOid; } ! else if (OidIsValid(binary_upgrade_next_toast_pg_class_oid) relkind ==
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian wrote: Bruce Momjian wrote: Robert Haas wrote: On Thu, Mar 31, 2011 at 12:11 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Thu, Mar 31, 2011 at 11:32 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: ?I think the maintenance overhead of an invisible variable is too much. A simple GUC or command-line switch isn't much code. I like the idea of a command-line switch. If you want to do that you should gereralize it as --binary-upgrade in case we have other needs for it. Yeah. Or we could do a binary_upgrade GUC which has the effect of forcibly suppressing autovacuum, and maybe other things later. I think that's a lot less hazardous than fiddling with the autovacuum GUC. I like the idea of a command-line flag because it forces everything to be affected, and cannot be turned on and off in sessions --- if you are doing a binary upgrade, locked-down is good. :-) The attached patch adds a new postmaster/postgres binary upgrade mode (-b) which disables autovacuum, allows only super-user connections, and prevents pg_upgrade_support oid assignment when not in upgrade mode. It also modifies pg_upgrade to use this new mode rather than play with trying to stop autovacuum. This does fix a very rare bug that could happen if autovacuum_freeze_max_age were set to maximum by the user. I think this should be applied to PG 9.1. One big problem with this patch is that it will not allow people to use pg_upgrade when upgrading from 9.1 alpha to beta. What I could do is to use the mode only on the new cluster for 9.1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian br...@momjian.us writes: The attached patch adds a new postmaster/postgres binary upgrade mode (-b) which disables autovacuum, allows only super-user connections, and prevents pg_upgrade_support oid assignment when not in upgrade mode. It also modifies pg_upgrade to use this new mode rather than play with trying to stop autovacuum. One big problem with this patch is that it will not allow people to use pg_upgrade when upgrading from 9.1 alpha to beta. Huh? Why would that be? Seems like you've done something in the wrong place if that's an issue. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: The attached patch adds a new postmaster/postgres binary upgrade mode (-b) which disables autovacuum, allows only super-user connections, and prevents pg_upgrade_support oid assignment when not in upgrade mode. It also modifies pg_upgrade to use this new mode rather than play with trying to stop autovacuum. One big problem with this patch is that it will not allow people to use pg_upgrade when upgrading from 9.1 alpha to beta. Huh? Why would that be? Seems like you've done something in the wrong place if that's an issue. Yeah, it is complicated. I don't really care if autovacuum runs on the old cluster (we only move the files while the server is down). We only want autovacuum not to mess with the relfrozenxids we set on the new cluster while the table file is empty. The other issue is that the old alpha binary will not know about the -b flag and hence will not start. This all came up when we were looking for the relfrozenxid bug, which we found as TOAST which has been fixed. This is a very small problem so maybe we just skip the fix for 9.1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Huh? Why would that be? Seems like you've done something in the wrong place if that's an issue. Yeah, it is complicated. I don't really care if autovacuum runs on the old cluster (we only move the files while the server is down). We only want autovacuum not to mess with the relfrozenxids we set on the new cluster while the table file is empty. The other issue is that the old alpha binary will not know about the -b flag and hence will not start. Well, once again, why are you trying to do that? It's not the source postmaster that needs this flag. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Huh? Why would that be? Seems like you've done something in the wrong place if that's an issue. Yeah, it is complicated. I don't really care if autovacuum runs on the old cluster (we only move the files while the server is down). We only want autovacuum not to mess with the relfrozenxids we set on the new cluster while the table file is empty. The other issue is that the old alpha binary will not know about the -b flag and hence will not start. Well, once again, why are you trying to do that? It's not the source postmaster that needs this flag. Well, consider that this also locks out non-super users so I figured it would be good to run the old and new in the same binary upgrade mode. Again, we can do just the new cluster for 9.1. I can also control the behavior based on the catalog version number, which seems the most logical. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
On Thu, 2011-04-21 at 18:22 -0400, Bruce Momjian wrote: I can also control the behavior based on the catalog version number, which seems the most logical. It seems like we want a simple use -b if available; else don't. Is that right? If so, switching based on the version seems reasonable. However, can you get the information directly from the bianry, rather than trying to infer it from the catalog version? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for pg_upgrade to turn off autovacuum
On Apr 21, 2011, at 6:22 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Huh? Why would that be? Seems like you've done something in the wrong place if that's an issue. Yeah, it is complicated. I don't really care if autovacuum runs on the old cluster (we only move the files while the server is down). We only want autovacuum not to mess with the relfrozenxids we set on the new cluster while the table file is empty. The other issue is that the old alpha binary will not know about the -b flag and hence will not start. Well, once again, why are you trying to do that? It's not the source postmaster that needs this flag. Well, consider that this also locks out non-super users so I figured it would be good to run the old and new in the same binary upgrade mode. Again, we can do just the new cluster for 9.1. I can also control the behavior based on the catalog version number, which seems the most logical. I think you are over-engineering this. Just use it for the new cluster only, full stop, and you'll be right as rain. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers