PostgreSQL 12 Upgrade

Windows Installation

PostgreSQL fresh Install on Windows

1. Refer Windows Installation to download InSights_Windows
2. Go to server2/postgresql-12.5-1-windows package
3. Run command "Postgresql-12-DB-SetUp.bat" from server2 file, this will create "data" folder inside postgresql-12.5-1-windows.
4. It will also replace pg_hba.conf and postgresql.conf inside data ,If any server specific changes please do the needed
5. Run command "Postgresql-12-DB-StartUp.bat" to start PostgreSQL database
6. Register PostgreSQL service using following command (make sure that user has Administrator privilege )

pg_ctl.exe register -N "postgresql12" -D "C:\Insights\InSights_Windows\Server2\postgresql-12.5-1-windows\pgsql\data" –w

1. Go to PgAdmin4 folder to and run postgresql-12.5-1-windows\pgsql\pgAdmin 4\bin\ pgAdmin4.exe to start pgAdmin4 utility.

To start manually:

pg_ctl -D "C:/InSights_Windows/Server2/postgresql-9.5.4-1/pgsql/data" start
To stop manually:
pg_ctl -D "C:/InSights_Windows/Server2/postgresql-9.5.4-1/pgsql/data" stop

PostgreSQL Upgrade on windows

1. Make sure that existing postgres 9.5 working
2. Take backup of existing database using following command from \postgresql-9.5.4-1\pgsql\bin\ folder

pg_dump.exe --file "C:\\Insights\\postgresBackUP\\insights.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "insight"
pg_dump.exe --file "C:\\Insights\\postgresBackUP\\grafana.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "grafana"

1. Stop existing postgres 9.5 database
2. Postgresql-12.5-1-windows package located under InSights_Windows/server2
3. Copy this package under InSights_Windows/server2 folder along with Postgresql-12-DB-SetUp.bat and Postgresql-12-DB-StartUp.bat files.
4. Run command "Postgresql-12-DB-SetUp.bat" from server2 file, this will create "data" folder inside postgresql-12.5-1-windows.
5. It will also replace pg_hba.conf and postgresql.conf inside data ,If any server specific changes please do the needed
6. Run command "Postgresql-12-DB-StartUp.bat" to start PostgreSQL database
7. Register PostgreSQL service using following command (make sure that user has Administrator privilege )

pg_ctl.exe register -N "postgresql12" -D "C:\Insights\InSights_Windows\Server2\postgresql-12.5-1-windows\pgsql\data" –w

1. Go to PgAdmin4 folder to and run postgresql-12.5-1-windows\pgsql\pgAdmin 4\bin\ pgAdmin4.exe to start pgAdmin4 utility
2. Now restore existing database backup using following command , \postgresql-12.5-1-windows\pgsql\bin\ folder

pg_restore.exe --host "localhost" --port "5432" --username "grafana" --password --dbname "insight" --verbose "C:\\Insights\\postgresBackUP
insight.sql"
pg_restore.exe --host "localhost" --port "5432" --username "grafana" --password --dbname "grafana" --verbose "C:\\Insights\\postgresBackUP
grafana.sql"

RHEL7/Centos Installation

PostgreSQL fresh Install on RHEL7/Centos

1. Refer PostgreSQL Installation to download installation script.
2. Change permission of script to 777 or 775
3. Execute script ./ insights_postgres12.sh
4. Check status of service using

sudo systemctl status postgresql-12.service

1. It will also replace pg_hba.conf and postgresql.conf inside data, If any server specific changes please do the needed
2. Stop the service using

sudo systemctl stop postgresql-12.service

1. Start service using

sudo systemctl start postgresql-12.service

1. Start pgAdmin4 script using flowing command

sudo /usr/pgadmin4/bin/setup-web.sh

PostgreSQL Upgrade on RHEL7

1. Make sure that existing postgres 9.5 working
2. Take backup of existing database using following command

/usr/pgsql-9.5/bin/pg_dump --file "/home/postgresBackup/insights.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "insight"
/usr/pgsql-9.5/bin/pg_dump --file "/home/postgresBackup/grafana.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "grafana"

1. Stop existing postgres 9.5 database
   1. Refer PostgreSQL Installation to download installation script.
2. Change permission of script to 777 or 775
3. Execute script ./ insights_postgres12.sh
4. Check status of service using

sudo systemctl status postgresql-12.service

1. It will also replace pg_hba.conf and postgresql.conf inside data, If any server specific changes please do the needed
2. Stop the service using

sudo systemctl stop postgresql-12.service

1. Start service using

sudo systemctl start postgresql-12.service

1. Start pgAdmin4 script using flowing command

sudo /usr/pgadmin4/bin/setup-web.sh

1. Now restore existing database backup using following command

/usr/pgsql-12/bin/pg_restore --host "localhost" --port "5432" --username "grafana" --no-password --dbname "insight" --verbose "/home/postgresBackup/insights.sql"
/usr/pgsql-12/bin/pg_restore --host "localhost" --port "5432" --username "grafana" --no-password --dbname "grafana" --verbose "/home/postgresBackup/grafana.sql"

Ubuntu Installation

PostgreSQL fresh Install on Ubuntu

1. Refer PostgreSQL Installation to download UBUNTU installation script.
2. Change permission of script to 777 or 775
3. Execute script ./insights_postgre12_Ubuntu.sh
4. Check status of service using

sudo systemctl status postgresql@12-main

1. It will also replace pg_hba.conf and postgresql.conf inside data, If any server specific changes please do the needed
2. Stop the service using

sudo systemctl stop postgresql@12-main

1. Start service using

sudo systemctl start postgresql@12-main

1. Start pgAdmin4 script using flowing command

sudo /usr/pgadmin4/bin/setup-web.sh

PostgreSQL Upgrade on Ubuntu

1. Make sure that existing postgres 9.5 working
2. Take backup of existing database using following command

/usr/lib/postgresql/12/bin/pg_dump --file "/home/ubuntu/postgresBackup/insights.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "insight"
/usr/lib/postgresql/12/bin/pg_dump --file "/home/ubuntu/postgresBackup/grafana.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "grafana"

1. Stop existing postgres 9.5 database
2. Refer PostgreSQL Installation to download UBUNTU installation script.
3. Change permission of script to 777 or 775
4. Execute script ./insights_postgre12_Ubuntu.sh
5. Check status of service using

sudo systemctl status postgresql@12-main

1. It will also replace pg_hba.conf and postgresql.conf inside data, If any server specific changes please do the needed
2. Stop the service using

sudo systemctl stop postgresql@12-main

1. Start service using

sudo systemctl start postgresql@12-main

1. Start pgAdmin4 script using flowing command
2. sudo /usr/pgadmin4/bin/setup-web.sh
3. Now restore existing database backup using following command

/usr/lib/postgresql/12/bin/pg_restore --host "localhost" --port "5432" --username "grafana" --no-password --dbname "insight" --verbose "/home/ubuntu/postgresBackup/insights.sql"
/usr/lib/postgresql/12/bin/pg_restore --host "localhost" --port "5432" --username "grafana" --no-password --dbname "grafana" --verbose "/home/ubuntu/postgresBackup/grafana.sql"