PostgreSQL server update
These instructions assume that the pgsql home directory is located in / usr / local / pgsql, and the data is located in / usr / local / pgsql / data.
If your configuration is different, replace the appropriate paths with your own. The internal data storage format is typically modified in each major release of PostgreSQL. Therefore, if you are upgrading an existing installation, a younger version of “8.3.x”, you must make a backup and then restore the data. If you update PostgreSQL “8.3.x”, the new version can use your current data files, so you can skip the backup and restore steps.
1. When making a backup, make sure that your database is not updated. This will not affect the integrity of the backup, but the modified data will not be entered. If necessary, edit the permissions in the / usr / local / pgsql / data / pg_hba.conf file to block access from everyone except you. To back up the database type
')
If you need to save OIDs, use the -o switch. To make a backup, you can use the pg_dumpall command from the version you are currently installing. For best results, however, use the pg_dumpall command from PostgreSQL 8.3.5, since this version contains bug fixes and improvements for older versions. The advice may seem strange, since you have not yet installed the new version, you can follow it if you plan to install the new version without first removing the old version. In this case, you can first complete the installation and then transfer the data. It will also reduce downtime.
2. Stop the old server:
or (depending on the installation of pgSQL on your system)
3. Rename or delete the old pgsql home directory. You can rename the directory instead of deleting it, in case there are problems during the installation, you can return to the old version. Keep in mind that the home directory of the database may take up substantial disk space. To rename the directory, use the command:
4. Install the new version of PostgreSQL (http://www.postgresql.org/docs/current/static/install-procedure.html)
5. Initialize the newly installed database (it is necessary to run commands from the user under which the database server will run, usually the user pgsql).
6. Restore the necessary pg_hba.conf and postgresql.conf settings.
7. Start the database server (again from the user under which the database server should work):
8. Finally, restore your backup data.
Use the new version of PgSQL!
Original articles
upd: before updating carefully think about what you are doing, because versions are not always compatible, and applications that previously worked with your server may stop working.
upd2: in case you are faced with the fact that your applications are incompatible with the updated version, install the previous version back and replace in it the / usr / local / pgsql / data directory with the previously saved one (if you did not delete it, but renamed it to 3 ).
If your configuration is different, replace the appropriate paths with your own. The internal data storage format is typically modified in each major release of PostgreSQL. Therefore, if you are upgrading an existing installation, a younger version of “8.3.x”, you must make a backup and then restore the data. If you update PostgreSQL “8.3.x”, the new version can use your current data files, so you can skip the backup and restore steps.
1. When making a backup, make sure that your database is not updated. This will not affect the integrity of the backup, but the modified data will not be entered. If necessary, edit the permissions in the / usr / local / pgsql / data / pg_hba.conf file to block access from everyone except you. To back up the database type
')
pg_dumpall > outputfileIf you need to save OIDs, use the -o switch. To make a backup, you can use the pg_dumpall command from the version you are currently installing. For best results, however, use the pg_dumpall command from PostgreSQL 8.3.5, since this version contains bug fixes and improvements for older versions. The advice may seem strange, since you have not yet installed the new version, you can follow it if you plan to install the new version without first removing the old version. In this case, you can first complete the installation and then transfer the data. It will also reduce downtime.
2. Stop the old server:
pg_ctl stopor (depending on the installation of pgSQL on your system)
/etc/rc.d/init.d/postgresql stop3. Rename or delete the old pgsql home directory. You can rename the directory instead of deleting it, in case there are problems during the installation, you can return to the old version. Keep in mind that the home directory of the database may take up substantial disk space. To rename the directory, use the command:
mv /usr/local/pgsql /usr/local/pgsql.old4. Install the new version of PostgreSQL (http://www.postgresql.org/docs/current/static/install-procedure.html)
5. Initialize the newly installed database (it is necessary to run commands from the user under which the database server will run, usually the user pgsql).
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data6. Restore the necessary pg_hba.conf and postgresql.conf settings.
7. Start the database server (again from the user under which the database server should work):
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data8. Finally, restore your backup data.
/usr/local/pgsql/bin/psql -d postgres -f outputfileUse the new version of PgSQL!
Original articles
upd: before updating carefully think about what you are doing, because versions are not always compatible, and applications that previously worked with your server may stop working.
upd2: in case you are faced with the fact that your applications are incompatible with the updated version, install the previous version back and replace in it the / usr / local / pgsql / data directory with the previously saved one (if you did not delete it, but renamed it to 3 ).
Source: https://habr.com/ru/post/66452/
All Articles