Thread: Upgrading from 9.0.11 to 9.3.5 on CentOS 6 (64 bit)
Hello, I have Postgresql from a few years ago. That's 9.0.11. During the vacuum it's basically crawling to its knees. While googling for this (it stops at "pg_classes" forever) I see Tom Lane suggested upgrading. So now I must. In doing so, can I follow these instructions? https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-a-centos-vps I want to make sure all my data remains exactly as it is, and the pgbouncer on top of PG (helps us a lot) also remains on the same port etc. Just want to confirm that whether I update via the RPM method, or the YUM method, that the settings in all the places will remain? Ideally, I don't want to be linking new paths and so on as I see in online instructions on blogs. Many of them (e.g., the official post here - http://wiki.postgresql.org/wiki/FAQ#What_is_the_upgrade_process_for_PostgreSQL.3F ) also speak of "clusters". I don't have any, or is my PG basically one cluster? Sorry for the noob question, but it would be great to get some simple to follow, step by step guidance. MySQL etc are so simple to upgrade! Many thanks, PK
On 7/31/2014 11:09 PM, Phoenix Kiula wrote: > I have Postgresql from a few years ago. That's 9.0.11. you can upgrade to 9.0.18 painlessly. 9.1 or .2 or .3, not quite so painless. > During the vacuum it's basically crawling to its knees. While googling > for this (it stops at "pg_classes" forever) I see Tom Lane suggested > upgrading. have you tried a vacuum full of the whole cluster, with your applications shut down? > So now I must. In doing so, can I follow these instructions? > https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-a-centos-vps those aren't upgrade instructions, those are first-time install instructions. > I want to make sure all my data remains exactly as it is, and the > pgbouncer on top of PG (helps us a lot) also remains on the same port > etc. Just want to confirm that whether I update via the RPM method, or > the YUM method, that the settings in all the places will remain? you will need to either pg_dumpall your old database 'cluster' and load this into the new version, or use pg_upgrade, which is a fair bit trickier but can do an in-place upgrade. if your databases aren't much over a few dozen gigabytes, pg_dumpall is probably simpler than pg_upgrade. if your databases are large, pg_dumpall -> psql restore may take a LONG time, so the pg_upgrade process may be more efficient. since you've never done this before, if you chose to go the pg_upgrade route, BACKUP EVERYTHING BEFORE YOU START. it may take several tries to get right. > Ideally, I don't want to be linking new paths and so on as I see in > online instructions on blogs. Many of them (e.g., the official post > here -http://wiki.postgresql.org/wiki/FAQ#What_is_the_upgrade_process_for_PostgreSQL.3F > ) also speak of "clusters". I don't have any, or is my PG basically > one cluster? in PG terminology, a 'cluster' is the set of databases in a single instance of the postgres server, with a single $PGDATA directory. poor choice of terms, 'instance' probably would have been more appropriate, but its too late to change. > Sorry for the noob question, but it would be great to get some simple > to follow, step by step guidance. MySQL etc are so simple to upgrade! mysql hasn't changed its core data formats in eons. but try to upgrade from MyISAM to InnoDB, good luck. -- john r pierce 37N 122W somewhere on the middle of the left coast
Thank you John. > you can upgrade to 9.0.18 painlessly. 9.1 or .2 or .3, not quite so > painless. What's the best way to get to 9.0.18, as a start? Is there a simple single command I can use? I'm on CentOS 6, 64bit. > have you tried a vacuum full of the whole cluster, with your applications > shut down? Not yet, not with the apps shut down entirely, but in read mode, yes. No new rows being added. SELECTs have to work as it's a high traffic website. > you will need to either pg_dumpall your old database 'cluster' and load this > into the new version, or use pg_upgrade, which is a fair bit trickier but > can do an in-place upgrade. if your databases aren't much over a few > dozen gigabytes, pg_dumpall is probably simpler than pg_upgrade. if your > databases are large, pg_dumpall -> psql restore may take a LONG time, so the > pg_upgrade process may be more efficient. Dread to use pg_upgrade after that confirmation of my fears. Our DB is around 200 GB. Even with pg_upgrade, will I have to once again tinker with all the conf files and authentication (which is md5 right now), change my passwords and do "template1" database stuff again? I saw the doc page on the postgresql.org site for pg_upgrade, but it presumes a lot of things in terms of knowledge. I just have one database "instance" or "cluster". The 10-15 tables are not complex. But they're large, as in over a billion rows now. All I need is for the upgrade to happen automatically, retaining my config and paths and whatnot (or clear instructions that work, step by step). Thanks!
On 8/1/2014 6:31 PM, Phoenix Kiula wrote: > What's the best way to get to 9.0.18, as a start? Is there a simple > single command I can use? I'm on CentOS 6, 64bit. assuming you installed 9.0 from the yum.postgresql.com respositories, then, `yum update postgresql90-server` and restart the postgresql-9.0 service should do nicely. if you installed 9.0 some other method, then I dunno. -- john r pierce 37N 122W somewhere on the middle of the left coast
Thanks...comments below. > assuming you installed 9.0 from the yum.postgresql.com respositories, then, > `yum update postgresql90-server` and restart the postgresql-9.0 service > should do nicely. This worked. Took me to 9.0.17 for some reason. I'm OK with this. But the "vacuum full" was a terrible idea. I just spent 2 tranches of 5 hours each waiting for it to work. Many websites/blogs mention NOT to run vacuum full at all. Instead, run cluster. Is this better then? My table is huge. Over a billion rows. The idea of "pg_dump" and then pg_restore of a table might work, followed by reindexing. But that would also cause serious downtime. Anything else I can do? Just adjust the autovacuum information for example? My current settings are as follows: autovacuum = on autovacuum_max_workers = 5 autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = 350 The table in question has over a billon rows. HOW do I know if this table is causing the issues? This is the only table that's heavily queried. Recently many times the PG server has been locked, and the pending queries have led to server outage. When I manually vacuumdb, this is the table where the process has stuck for hours. So I need to tune this table back to its usual performance. Appreciate any ideas! I'm sure there are much larger tables in the world than mine. What do they do? (Apart from replication etc) Thanks.