Re: Column type modification in big tables
On Tue, Aug 13, 2024 at 7:39 PM Greg Sabino Mullane wrote: > On Sat, Aug 10, 2024 at 5:06 PM Lok P wrote: > > >> Can someone through some light , in case we get 5-6hrs downtime for this >> change , then what method should we choose for this Alter operation? >> > > We can't really answer that. Only you know what resources you have, what > risk/reward you are willing to handle, and how long things may take. For > that latter item, your best bet is to try this out on the same/similar > hardware and see how long it takes.* Do a smaller table and extrapolate > if you need to. * > Hello Greg, In terms of testing on sample data and extrapolating, as i picked the avg partition sizeof the table (which is ~20GB) and i created a non partitioned table with exactly same columns and populated with similar data and also created same set of indexes on it and the underlying hardware is exactly same as its on production. I am seeing it's taking ~5minutes to alter all the four columns on this table. So we have ~90 partitions in production with data in them and the other few are future partitions and are blank. (Note- I executed the alter with "work_mem=4GB, maintenance_work_mem=30gb, max_parallel_worker_per_gather=8, max_parallel_maintenance_worker =16" ) So considering the above figures , can i safely assume it will take ~90*5minutes= ~7.5hours in production and thus that many hours of downtime needed for this alter OR do we need to consider any other factors or activity here?
Re: What is the best way to upgrade pgAdmin on Windows?
On Thu, Aug 15, 2024 at 2:35 AM 毛毛 wrote: > Hey there, > > I just found out my pgAdmin is version 7.6, while the current version is > 8.6. Looks like it's time for an upgrade! > https://www.pgadmin.org/download/pgadmin-4-windows/ The latest is 8.10. > The notification I got had a link to the EDB site with a bunch of > PostgreSQL installers listed. Thing is, I don't want to mess up my > database. > What does PgAdmin have to do with your database? (Unless EDB only bundles *everything* together, in which case you should *ask EDB*.) > So I figured it'd be smart to learn some best practices for upgrading > before I dive in. > > Any tips or advice on how to do this safely? Thanks in advance! > I'd install 8.10 from https://www.postgresql.org/ftp/pgadmin/pgadmin4/v8.10/windows/, and expect it to overwrite the old version, just like every other Windows and Linux installer does. -- Death to America, and butter sauce. Iraq lobster!
Re: What is the best way to upgrade pgAdmin on Windows?
On 8/14/24 23:34, 毛毛 wrote: Hey there, I just found out my pgAdmin is version 7.6, while the current version is 8.6. Looks like it's time for an upgrade! Per https://www.pgadmin.org/download/pgadmin-4-windows/ The current version is 8.10. The notification I got had a link to the EDB site with a bunch of PostgreSQL installers listed. Thing is, I don't want to mess up my database. So I figured it'd be smart to learn some best practices for upgrading before I dive in. pgAdmin4 != Postgres, it is just a GUI client for Postgres. How did you originally install pgAdmin4? Any tips or advice on how to do this safely? Thanks in advance! -- Adrian Klaver adrian.kla...@aklaver.com
Re: Column type modification in big tables
> On 15 Aug 2024, at 14:15, Lok P wrote: (…) > Hello Greg, > > In terms of testing on sample data and extrapolating, as i picked the avg > partition sizeof the table (which is ~20GB) and i created a non partitioned > table with exactly same columns and populated with similar data and also > created same set of indexes on it and the underlying hardware is exactly same > as its on production. I am seeing it's taking ~5minutes to alter all the four > columns on this table. So we have ~90 partitions in production with data in > them and the other few are future partitions and are blank. (Note- I executed > the alter with "work_mem=4GB, maintenance_work_mem=30gb, > max_parallel_worker_per_gather=8, max_parallel_maintenance_worker =16" ) > > So considering the above figures , can i safely assume it will take > ~90*5minutes= ~7.5hours in production and thus that many hours of downtime > needed for this alter OR do we need to consider any other factors or activity > here? Are all those partitions critical, or only a relative few? If that’s the case, you could: 1) detach the non-critical partitions 2) take the system down for maintenance 3) update the critical partitions 4) take the system up again 5) update the non-critical partitions 6) re-attach the non-critical partitions That could shave a significant amount of time off your down-time. I would script the detach and re-attach processes first, to save some extra. Admittedly, I haven’t actually tried that procedure, but I see no reason why it wouldn’t work. Apart perhaps, from inserts happening that should have gone to some of those detached partitions. Maybe those could be sent to a ‘default’ partition that gets detached at step 7, after which you can insert+select those from the default into the appropriate partitions? But you were going to test that first anyway, obviously. Alban Hertroys -- There is always an exception to always.
Re: Column type modification in big tables
On Thu, Aug 15, 2024 at 9:18 PM Alban Hertroys wrote: > > > On 15 Aug 2024, at 14:15, Lok P wrote: > > (…) > Are all those partitions critical, or only a relative few? > > If that’s the case, you could: > 1) detach the non-critical partitions > 2) take the system down for maintenance > 3) update the critical partitions > 4) take the system up again > 5) update the non-critical partitions > 6) re-attach the non-critical partitions > > That could shave a significant amount of time off your down-time. I would > script the detach and re-attach processes first, to save some extra. > > Thank you so much. The partition table which we are planning to apply the ALTER script is a child table to another big partition table. And we have foreign key defined on table level but not partition to partition. So will detaching the partitions and then altering column of each detached partition and then re-attaching will revalidate the foreign key again? If that is the case then the re-attaching partition step might consume a lot of time. Is my understanding correct here?
Re: Column type modification in big tables
On Fri, Aug 16, 2024 at 2:04 AM Lok P wrote: > > On Thu, Aug 15, 2024 at 9:18 PM Alban Hertroys wrote: > >> >> > On 15 Aug 2024, at 14:15, Lok P wrote: >> >> (…) >> Are all those partitions critical, or only a relative few? >> >> If that’s the case, you could: >> 1) detach the non-critical partitions >> 2) take the system down for maintenance >> 3) update the critical partitions >> 4) take the system up again >> 5) update the non-critical partitions >> 6) re-attach the non-critical partitions >> >> That could shave a significant amount of time off your down-time. I would >> script the detach and re-attach processes first, to save some extra. >> >> > Thank you so much. > > The partition table which we are planning to apply the ALTER script is a > child table to another big partition table. And we have foreign key > defined on table level but not partition to partition. So will detaching > the partitions and then altering column of each detached partition and then > re-attaching will revalidate the foreign key again? If that is the case > then the re-attaching partition step might consume a lot of time. Is my > understanding correct here? > Additionally , if we are okay with the 7.5hrs of down time , is my calculation/extrapolation of total time consumption based on a sample table, for direct alter, accurate? Because, in that case , I was thinking it's less complex and also less error prone to just do it in a single alter command rather than going for multiple steps of detach, alter, attach partition.
PostgreSQL Upgrade Issue - Undefined Symbol Error
Hi All, I am encountering an error after completion of PostgreSQL upgradation from version 13.8 to 15.6. Trying to describe a single table, I am receiving an error. psql -d xyz -->> \dt student* The error message is as follows: ``` psql: symbol lookup error: psql: undefined symbol: PQmblenBounded ``` Could you please assist in resolving this issue? Thanks and regards, Vivek Gadge
Re: PostgreSQL Upgrade Issue - Undefined Symbol Error
On 8/15/24 04:03, Vivek Gadge wrote: Hi All, I am encountering an error after completion of PostgreSQL upgradation from version 13.8 to 15.6. Trying to describe a single table, I am receiving an error. psql -d xyz -->> \dt student* The error message is as follows: ``` psql: symbol lookup error: psql: undefined symbol: PQmblenBounded ``` Could you please assist in resolving this issue? Best bet you are using the 13.8 version of psql to work on 15.6 instance of Postgres. Do psql -V to verify what you are running. Thanks and regards, Vivek Gadge -- Adrian Klaver adrian.kla...@aklaver.com
Re: PostgreSQL Upgrade Issue - Undefined Symbol Error
Vivek Gadge writes: > I am encountering an error after completion of PostgreSQL upgradation from > version 13.8 to 15.6. Trying to describe a single table, I am receiving an > error. > psql: symbol lookup error: psql: undefined symbol: PQmblenBounded This indicates that your psql executable is linking to an old version of libpq.so. (Running "ldd" on the psql file should confirm that.) You may have a problem with the library search path being used by the dynamic loader. regards, tom lane
Re:Re: What is the best way to upgrade pgAdmin on Windows?
Hi Ron, The notification I got had a link to the EDB site with a bunch of PostgreSQL installers listed. Thing is, I don't want to mess up my database. What does PgAdmin have to do with your database? I didn't know there was a standalone installer for pgAdmin. This link brought me to this page: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads I could not find a standalone installer for pgAdmin so I thought I can only upgrade via one of the all-in-one installer. Hi Adrian, How did you originally install pgAdmin4? I started by clicking Download the installer on this page: PostgreSQL: Windows installers I ran the installer downloaded and checked the box for pgAdmin during the installation. Hi Anthony, If your version of pgAdmin was installed while you installed PostgreSQL itself via the EDB installer for Windows, then the easiest upgrade option is to navigate under Start to Uninstall Programs and choose to uninstall PostgreSQL. When the uninstaller fires up, it should give you the option to specify which components to uninstall. You can choose to uninstall ONLY pgAdmin. I do this often and have never had an issue. It avoids getting two versions of pgAdmin on your Windows machine. This is exactly what I did. I carried out the installation exactly as described in your book. What should I do after the uninstallation? Should I download and run installer available on: Download (pgadmin.org) Thank you! At 2024-08-16 04:38:26, "Anthony DeBarros" wrote: Howdy, On Thu, Aug 15, 2024 at 2:35 AM 毛毛 wrote: Hey there, I just found out my pgAdmin is version 7.6, while the current version is 8.6. Looks like it's time for an upgrade! The notification I got had a link to the EDB site with a bunch of PostgreSQL installers listed. Thing is, I don't want to mess up my database. So I figured it'd be smart to learn some best practices for upgrading before I dive in. Any tips or advice on how to do this safely? Thanks in advance! If your version of pgAdmin was installed while you installed PostgreSQL itself via the EDB installer for Windows, then the easiest upgrade option is to navigate under Start to Uninstall Programs and choose to uninstall PostgreSQL. When the uninstaller fires up, it should give you the option to specify which components to uninstall. You can choose to uninstall ONLY pgAdmin. I do this often and have never had an issue. It avoids getting two versions of pgAdmin on your Windows machine.
