TutorialDBA - Support | Training | Consultant

In this blog we are going to upgrade the postgresql server from 9.5 to 11.3 . We are upgraded the postgres server by using pg_upgrade utility as well as logical backup method , you can follow anyone  of the method . Collect the server details before proceed  upgrade activity.  Title PostgreSQL VERSION 9.5 PostgreSQL VERSION 11.3 DATA DIRECTORY /data_9.5 /data_11.3 BIN path /opt/postgresql/9.5 /opt/postgresql/11.3 PORT 50000 5432 PREREQUEST : =========== Step 1. Check the application backend connection if any application connected , Disconnect the application from DB server. Below command will be helpful to checking backend connection : template1=# select datname, count(*) from pg_stat_activity group by datname; datname | count -----------+------- testdb | 1 template1 | 0 (2 rows) template1=# select datname, numbackends from pg_stat_database; datname | numbackends -----------+------------- testdb | 1 template1 | ...

PostgreSQL Upgrading Type: pg_upgrade utility pg_dumpall -taking full cluster backup and restoring new version of software slony  -upgrading postgres with Zero Down Time pg_upgrade utility In this article on upgrading postgresql using pg_upgrade utility, I used a 9.2 instance that was installed using the methodology in that earlier article.  If your Postgresql instance was installed using a different manner you will likely need to modify these instructions to make it work. Step 1 – Complete Backup of Postgresql System Please use your typical backup procedures for backing up your system prior to using these instructions.  Use pg_dump, a full filesystem and operating system dump or whatever your typical procedures are so that you can get things back up and running should something not go wrong. Step 2 – Install New Version of Postgres The first step in that process is to download the Postgresql repository for the new version that we are installing. ...

combination of these two resources: https://gist.github.com/tamoyal/2ea1fcdf99c819b4e07d and http://www.gab.lc/articles/migration_postgresql_9-3_to_9-4 The  second  one  helped more  then the first one. Also to not, don't follow the steps as is as some are not necessary. Also, if you are not being able to backup the data via postgres console, you can use alternative approach, and backup it with pgAdmin 3 or some other program, like I did in my case. Also, the link:  https://help.ubuntu.com/stable/serverguide/postgresql.html  Helped to set the encrypted password and set md5 for authenticating the postgres user. After all is done, to check the postgres  server  version run in terminal: sudo - u postgres psql postgres After entering the password run in postgres terminal: SHOW SERVER_VERSION ; It will output something like: server_version ---------------- 9.4.5 For setting and starting postgres I have used command: > su...

I just upgraded PostgreSQL 9.5 to 9.6 on Ubuntu and thought I'd share my findings, as there are a couple of OS/package-specific nuances of which to be aware. ( I didn't want to have to dump and restore data manually , so several of the other answers here were not viable.) In short, the process consists of installing the new version of PostgreSQL alongside the old version (e.g., 9.5 and 9.6), and then running the  pg_upgrade  binary, which is explained in (some) detail at  https://www.postgresql.org/docs/9.6/static/pgupgrade.html  . The only "tricky" aspect of  pg_upgrade  is that failure to pass the correct value for an argument, or failure to be logged-in as the correct user or  cd  to the correct location before executing a command, may lead to cryptic error messages. On Ubuntu (and probably Debian), provided you are using the "official" repo,  deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main , and provided you haven'...