Postgres:Upgrading - Revision history

Daqasimi18: /* Upgrading PostgreSQL Version 9.6 to 14.3 */

2022-08-03T20:29:25Z

Upgrading PostgreSQL Version 9.6 to 14.3

← Older revision		Revision as of 20:29, 3 August 2022
Line 27:		Line 27:
	Last updated August 2022		Last updated August 2022

	PostgreSQL, also known as Postgres, is an open-source relational database system that provides tables and frames for organizing data. Originally Bowie was using PostgreSQL <code>9.6</code>, which was released in 2016. At Earlham, some of the data from the Iceland Epic program, computer science department, math department, and some other science majors use PostgreSQL on Bowie as a way to manage and structure data. Most clients who used PostgreSQL on Bowie recently faced PostgreSQL version incompatibility. Therefore, PostgreSQL version <code>9.6</code> was replaced with the latest version of PostgreSQL <code>14.3</code> to bring compatibility for new database commands, data management, and keeping things up to date. The following steps were taken to remove the old version of Postgres on Bowie and transfer all the data from the old version to the newly installed version;		PostgreSQL, also known as Postgres, is an open-source relational database system that provides tables and frames for organizing data. Originally Bowie was using PostgreSQL <code>9.6</code>, which was released in 2016. At Earlham, some of the data from the Iceland Epic program, computer science department, math department, and some other science majors use PostgreSQL on Bowie as a way to manage and structure data. Most clients who used PostgreSQL <code>9.6</code>on Bowie recently faced PostgreSQL version incompatibility. Therefore, PostgreSQL version <code>9.6</code> was replaced with the latest version of PostgreSQL <code>14.3</code> to bring compatibility for new database commands, data management, and keeping things up to date. The following steps were taken to remove the old version of Postgres on Bowie and transfer all the data from the old version to the newly installed version;

	# In the beginning, there were two cluster versions of PostgreSQL. Cluster one was PostgreSQL <code>9.6</code> and cluster two was PostgreSQL <code>14.3</code>. As a first step, all databases on PostgreSQL <code>9.6</code> were dumped into a file in <code>/postgres/backup/dumpall_backup/all_databases.sql</code> using <code>pg_dumpall -u postgres > all_databases.sql</code> command.		# In the beginning, there were two cluster versions of PostgreSQL. Cluster one was PostgreSQL <code>9.6</code> and cluster two was PostgreSQL <code>14.3</code>. As a first step, all databases on PostgreSQL <code>9.6</code> were dumped into a file in <code>/postgres/backup/dumpall_backup/all_databases.sql</code> using <code>pg_dumpall -u postgres > all_databases.sql</code> command.

Daqasimi18: /* Upgrading PostgreSQL Version 9.6 to 14.3 */

2022-08-03T20:28:14Z

Upgrading PostgreSQL Version 9.6 to 14.3

← Older revision		Revision as of 20:28, 3 August 2022
Line 27:		Line 27:
	Last updated August 2022		Last updated August 2022

	PostgreSQL, also known as Postgres, is an open-source relational database system that provides tables and frames for organizing data. Originally Bowie was using PostgreSQL <code>9.6</code>, which was released in 2016. At Earlham, some of the data from the Iceland Epic program, computer science department, math department, and some other science majors use PostgreSQL on Bowie as a way to manage and structure data. Most ~~college~~ clients who used PostgreSQL recently faced PostgreSQL version incompatibility. Therefore, PostgreSQL version <code>9.6</code> was replaced with the latest version of PostgreSQL <code>14.3</code> to bring compatibility for new database commands, data management, and keeping things up to date. The following steps were taken to remove the old version of Postgres on Bowie and transfer all the data from the old version to the newly installed version;		PostgreSQL, also known as Postgres, is an open-source relational database system that provides tables and frames for organizing data. Originally Bowie was using PostgreSQL <code>9.6</code>, which was released in 2016. At Earlham, some of the data from the Iceland Epic program, computer science department, math department, and some other science majors use PostgreSQL on Bowie as a way to manage and structure data. Most clients who used PostgreSQL on Bowie recently faced PostgreSQL version incompatibility. Therefore, PostgreSQL version <code>9.6</code> was replaced with the latest version of PostgreSQL <code>14.3</code> to bring compatibility for new database commands, data management, and keeping things up to date. The following steps were taken to remove the old version of Postgres on Bowie and transfer all the data from the old version to the newly installed version;

	# In the beginning, there were two cluster versions of PostgreSQL. Cluster one was PostgreSQL <code>9.6</code> and cluster two was PostgreSQL <code>14.3</code>. As a first step, all databases on PostgreSQL <code>9.6</code> were dumped into a file in <code>/postgres/backup/dumpall_backup/all_databases.sql</code> using <code>pg_dumpall -u postgres > all_databases.sql</code> command.		# In the beginning, there were two cluster versions of PostgreSQL. Cluster one was PostgreSQL <code>9.6</code> and cluster two was PostgreSQL <code>14.3</code>. As a first step, all databases on PostgreSQL <code>9.6</code> were dumped into a file in <code>/postgres/backup/dumpall_backup/all_databases.sql</code> using <code>pg_dumpall -u postgres > all_databases.sql</code> command.

Daqasimi18: /* Upgrading PostgreSQL 9.6 to 14.3 */

2022-08-03T20:25:54Z

Upgrading PostgreSQL 9.6 to 14.3

← Older revision		Revision as of 20:25, 3 August 2022
Line 24:		Line 24:
	<tt>createdb pgsql && psql < pg.dump</tt>		<tt>createdb pgsql && psql < pg.dump</tt>

	== Upgrading PostgreSQL ~~<code>~~9.6~~</code>~~ to ~~<code>~~14.3~~</code>~~ ==		== Upgrading PostgreSQL Version 9.6 to 14.3 ==
	Last updated August 2022		Last updated August 2022

Line 33:		Line 33:
	# Then created a PostGIS extension in PostgreSQL <code>14.3</code> to create geographic and location capabilities for data.		# Then created a PostGIS extension in PostgreSQL <code>14.3</code> to create geographic and location capabilities for data.
	# Finally restored the <code>/postgres/backup/dumpall_backup/all_databases.sql</code> file into the newly installed Postgres version of <code>14.3</code>.		# Finally restored the <code>/postgres/backup/dumpall_backup/all_databases.sql</code> file into the newly installed Postgres version of <code>14.3</code>.
	# To ~~make sure~~ data have been properly restored, some commands were tried to check accuracy. E.g., from Hopper server: <code>psql -h bowie.cs.earlham.edu -d field_science -U fieldsci</code> then <code>select * from fieldday_trip</code>. If the command returns a few lines of relations and databases, it assures us that the backed-up data is restored properly.		# To ensure data have been properly restored, some commands were tried to check accuracy. E.g., from Hopper server: <code>psql -h bowie.cs.earlham.edu -d field_science -U fieldsci</code> then <code>select * from fieldday_trip</code>. If the command returns a few lines of relations and databases, it assures us that the backed-up data is restored properly.

Daqasimi18 at 20:24, 3 August 2022

2022-08-03T20:24:39Z

← Older revision		Revision as of 20:24, 3 August 2022
Line 23:		Line 23:
	* Start the restore as the pgsql user:		* Start the restore as the pgsql user:
	<tt>createdb pgsql && psql < pg.dump</tt>		<tt>createdb pgsql && psql < pg.dump</tt>

			== Upgrading PostgreSQL <code>9.6</code> to <code>14.3</code> ==
			Last updated August 2022

			PostgreSQL, also known as Postgres, is an open-source relational database system that provides tables and frames for organizing data. Originally Bowie was using PostgreSQL <code>9.6</code>, which was released in 2016. At Earlham, some of the data from the Iceland Epic program, computer science department, math department, and some other science majors use PostgreSQL on Bowie as a way to manage and structure data. Most college clients who used PostgreSQL recently faced PostgreSQL version incompatibility. Therefore, PostgreSQL version <code>9.6</code> was replaced with the latest version of PostgreSQL <code>14.3</code> to bring compatibility for new database commands, data management, and keeping things up to date. The following steps were taken to remove the old version of Postgres on Bowie and transfer all the data from the old version to the newly installed version;

			# In the beginning, there were two cluster versions of PostgreSQL. Cluster one was PostgreSQL <code>9.6</code> and cluster two was PostgreSQL <code>14.3</code>. As a first step, all databases on PostgreSQL <code>9.6</code> were dumped into a file in <code>/postgres/backup/dumpall_backup/all_databases.sql</code> using <code>pg_dumpall -u postgres > all_databases.sql</code> command.
			# After making sure all the data were backed up properly, then deleted Postgres cluster <code>9.6</code>.
			# Then created a PostGIS extension in PostgreSQL <code>14.3</code> to create geographic and location capabilities for data.
			# Finally restored the <code>/postgres/backup/dumpall_backup/all_databases.sql</code> file into the newly installed Postgres version of <code>14.3</code>.
			# To make sure data have been properly restored, some commands were tried to check accuracy. E.g., from Hopper server: <code>psql -h bowie.cs.earlham.edu -d field_science -U fieldsci</code> then <code>select * from fieldday_trip</code>. If the command returns a few lines of relations and databases, it assures us that the backed-up data is restored properly.

Skylar at 00:43, 21 December 2005

2005-12-21T00:43:58Z

← Older revision		Revision as of 00:43, 21 December 2005
Line 22:		Line 22:
	<tt>pg_ctl start</tt>		<tt>pg_ctl start</tt>
	* Start the restore as the pgsql user:		* Start the restore as the pgsql user:
	<tt>psql < pg.dump</tt>		<tt>createdb pgsql && psql < pg.dump</tt>

Skylar at 00:43, 21 December 2005

2005-12-21T00:43:28Z

← Older revision		Revision as of 00:43, 21 December 2005
Line 15:		Line 15:
	<tt>cp $HOME/data/*.{conf,key,crt} $HOME</tt>		<tt>cp $HOME/data/*.{conf,key,crt} $HOME</tt>
	* Upgrade the server from the port in /usr/ports/databases/postgresql-''version''-server		* Upgrade the server from the port in /usr/ports/databases/postgresql-''version''-server
			* Delete the old database directory:
			<tt>rm -fr $HOME/data</tt>
			* Remake it:
			<tt>initdb && cp $HOME/*.{conf,crt,key} $HOME/data</tt>
	* Start the server as the pgsql user:		* Start the server as the pgsql user:
	<tt>pg_ctl start</tt>		<tt>pg_ctl start</tt>
	* Start the restore as the pgsql user:		* Start the restore as the pgsql user:
	<tt>psql < pg.dump</tt>		<tt>psql < pg.dump</tt>

Skylar at 00:40, 21 December 2005

2005-12-21T00:40:15Z

New page

== FreeBSD ==

* Upgrade the clients utilities from the ports in /usr/ports/databases/postgresql-''version''-client
* Shutdown the server and restart it.
<tt>pg_ctl -m fast stop && pg_ctl start</tt>

You might have to do a

<tt>postmaster -D /usr/local/pgsql/data</tt>

if there's a problem with the new client utilities when you're starting the server.
* Do a dump of all databases as the pgsql user:
<tt>pg_dumpall > pg.dump</tt>
* Backup all the configuration files. If you are the pgsql user:
<tt>cp $HOME/data/*.{conf,key,crt} $HOME</tt>
* Upgrade the server from the port in /usr/ports/databases/postgresql-''version''-server
* Start the server as the pgsql user:
<tt>pg_ctl start</tt>
* Start the restore as the pgsql user:
<tt>psql < pg.dump</tt>