Thread: Upgrading from PostgreSQL 9.3 to 11.6
I have a PostgreSQL 9.3 installation that I finally have time to migrate to a currently supported version. The server that the installation is installed on is also at its end of service. Can anyone help me with completing this conversion as securely and efficiently as possible with the least amount of down time? Also, once it has been converted over, is there any best practice for ensuring that there has been no corruption? The database is 600GB in size. We do not have any custom types being used. Thanks for any direction.
On 12/20/19 5:58 AM, tony@exquisiteimages.com wrote: > I have a PostgreSQL 9.3 installation that I finally have time to migrate > to a currently supported version. The server that the installation is > installed on is also at its end of service. > > Can anyone help me with completing this conversion as securely and > efficiently as possible with the least amount of down time? Also, once > it has been converted over, is there any best practice for ensuring that > there has been no corruption? The choices off the top of my head: 1) pg_dump/pg_restore using the 11.6 binaries of both. 2) Using pg_upgrade(www.postgresql.org/docs/11/pgupgrade.html) from 11.6. 3) Setting up Slony(http://www.slony.info/). Downtime is hard to predict, especially if you have not done a procedure before. The simplest would be the dump/restore. I would go through the Release Notes for the first release of each major version 9.4, 9.5, 9.6 and 11 to check out any behavior changes that might trip you up. Given the number of versions you are stepping over setting up a test instance of 11.6 to do the upgrade against would be a good thing to do. > > The database is 600GB in size. We do not have any custom types being used. > > Thanks for any direction. > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 2019-12-20 09:02, Justin wrote: > Hi Tony > > Can you describe the database in greater detail? how many tables, > type of data, how big is the down time window? > > So this will be on new server hardware, and new DB install, what OS? > and extensions being used, has there been any testing > > with apps on latter versions of PostgreSQL??? > > On Fri, Dec 20, 2019 at 8:58 AM <tony@exquisiteimages.com> wrote: > >> I have a PostgreSQL 9.3 installation that I finally have time to >> migrate >> to a currently supported version. The server that the installation >> is >> installed on is also at its end of service. >> >> Can anyone help me with completing this conversion as securely and >> efficiently as possible with the least amount of down time? Also, >> once >> it has been converted over, is there any best practice for ensuring >> that >> there has been no corruption? >> >> The database is 600GB in size. We do not have any custom types being >> >> used. >> >> Thanks for any direction. Thanks for the reply! New Server OS: Ubuntu Server 18.04.3 LTS Schemas: 9,000 Total Tables: 900,000 Data is mostly transactional detail (90%), some summary tables (8%), and basic client demographic data (2%) Everyone wants the down time to be zero, but I don't think that is really possible. I am thinking about 4 hours on a weekend night is going to be what I can get approved. My plan was to go through the conversion process once to see if everything goes well. I would then use the new server as a test server and run our full battery of applications against it to make sure that we obtain the same results as from a back copy of the live database.