My Database Friend PostgreSQL

Next: Import nginx accesslog to PostgreSQL UP: TOC

This is a straight forward guide for people who want to safely upgrade PostgreSQL Version 9.6 to 10.

IMPORTANT: First take a snapshot or save the entire virtual machine.

Get Information about the environment

pkg info | grep sql php71-pdo_pgsql-7.1.20 The pdo_pgsql shared extension for php php71-pgsql-7.1.20 The pgsql shared extension for php postgresql96-client-10.5 PostgreSQL database (client) postgresql10-contrib-10.5 The contrib utilities from the PostgreSQL distribution postgresql96-server-10.5 PostgreSQL is the most advanced open-source database available anywhere

Show Version of the PostgreSQL database

/usr/local/bin/psql -U postgresql -d dbname -t --command "show server_version"

Get a list of the PostgreSQL databases

usr/local/bin/psql -U postgresql -d dbname -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+----------+----------+---------+-------+----------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + template1 | postgres | UTF8 | C | C | =c/postgres + wnk2016 | postgres | UTF8 | C | C |

Dump all PostgreSQL databases

/usr/local/bin/pg_dump --create --encoding=UTF8 --column-inserts --dbname=wnk2016 --format=p --inserts --username=postgres --file=/usr/local/www/wnk20181025.sql

Check if the dump exists

ls -altr /usr/local/www/*.sql -rw-r--r-- 1 root wheel 122116150 Dec 30 10:57 /usr/local/www/wnk20171230.sql Output a location of the Configuration Directories pg_config BINDIR = /usr/local/bin DOCDIR = /usr/local/share/doc/postgresql HTMLDIR = /usr/local/share/doc/postgresql INCLUDEDIR = /usr/local/include PKGINCLUDEDIR = /usr/local/include/postgresql INCLUDEDIR-SERVER = /usr/local/include/postgresql/server LIBDIR = /usr/local/lib PKGLIBDIR = /usr/local/lib/postgresql LOCALEDIR = /usr/local/share/locale MANDIR = /usr/local/man SHAREDIR = /usr/local/share/postgresql SYSCONFDIR = /usr/local/etc/postgresql PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/ CONFIGURE = '--with-libraries=/usr/local/lib' '--with-includes=/usr/local/include' '--enable-thread-safety' '--disable-debug' '--enable-nls' '--without-pam' '--with-openssl' '--without-gssapi' '--prefix=/usr/local' '--localstatedir=/var' '--mandir=/usr/local/man' '--infodir=/usr/local/info/' '--build=amd64-portbld-freebsd11.1' 'build_alias=amd64-portbld-freebsd11.1' 'CC=cc' 'CFLAGS=-O2 -pipe -fstack-protector -fno-strict-aliasing' 'LDFLAGS= -L/usr/local/lib -lpthread -L/usr/local/lib -Wl,-rpath,/usr/local/lib -fstack-protector' 'LIBS=' 'CPPFLAGS=-I/usr/local/include' 'CPP=cpp' 'LDFLAGS_SL=' CC = cc CPPFLAGS = -DFRONTEND -I/usr/local/include -I/usr/local/include -I/usr/local/include CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -pipe -fstack-protector -fno-strict-aliasing CFLAGS_SL = -fPIC -DPIC LDFLAGS = -L../../src/common -L/usr/local/lib -lpthread -L/usr/local/lib -Wl,-rpath,/usr/local/lib -fstack-protector -L/usr/local/lib -Wl,--as-needed -Wl,-R'/usr/local/lib' LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lintl -lssl -lcrypto -lz -lreadline -lcrypt -lm -lpthread VERSION = PostgreSQL 9.6.6

Postgresql Data Dir: /var/db/postgres/data96/

Update PostgreSQL default version in FreeBSD config files

At the time of writing the default version for postgreSQL was version 9.3. However, we need version 10.

vi /usr/ports/Mk/

Change the following values

# Possible values: 9.2, 9.3, 9.4, 9.5, 9.6 PGSQL_DEFAULT?= 9.6

Stop the Services

service postgresql stop

Delete old Packages

pkg delete -fy postgresql10-client-10.5;
pkg delete -fy postgresql10-contrib-10.5;
pkg delete -fy postgresql10-server-10.5 Updating database digests format: 100% Checking integrity... done (0 conflicting) Deinstallation has been requested for the following 1 packages (of 0 packages in the universe): Installed packages to be REMOVED: postgresql10-client-10.5 Number of packages to be removed: 1 The operation will free 11 MiB. [1/1] Deinstalling postgresql10-client-10.5... [1/1] Deleting files for postgresql10-client-10.5: 100% root@wnk20160809:~ # pkg delete -fy postgresql10-server-10.5 Checking integrity... done (0 conflicting) Deinstallation has been requested for the following 1 packages (of 0 packages in the universe): Installed packages to be REMOVED: postgresql10-server-10.5 Number of packages to be removed: 1 The operation will free 20 MiB. [1/1] Deinstalling postgresql10-server-10.5... [1/1] Deleting files for postgresql10-server-10.5: 100% ==> You should manually remove the "postgres" user. ==> You should manually remove the "postgres" group

Install PostgreSQL 11

cd /usr/ports/databases/postgresql11-server/ && make install clean
cd /usr/ports/databases/postgresql11-client/ && make install clean
Please note that if you use the rc script, /usr/local/etc/rc.d/postgresql, to initialize the database, unicode (UTF-8) will be used to store character data by default. Set postgresql_initdb_flags or use login.conf settings described below to alter this behaviour. See the start rc script for more info. To set limits, environment stuff like locale and collation and other things, you can set up a class in /etc/login.conf before initializing the database. Add something similar to this to /etc/login.conf: --- postgres:\ :lang=en_US.UTF-8:\ :setenv=LC_COLLATE=C:\ :tc=default: --- and run `cap_mkdb /etc/login.conf'. Then add 'postgresql_class="postgres"' to /etc/rc.conf. ====================================================================== To initialize the database, run /usr/local/etc/rc.d/postgresql initdb You can then start PostgreSQL by running: /usr/local/etc/rc.d/postgresql start For postmaster settings, see ~pgsql/data/postgresql.conf NB. FreeBSD's PostgreSQL port logs to syslog by default See ~pgsql/data/postgresql.conf for more info ====================================================================== To run PostgreSQL at startup, add 'postgresql_enable="YES"' to /etc/rc.conf ===> SECURITY REPORT: This port has installed the following files which may act as network servers and may therefore pose a remote security risk to the system. /usr/local/bin/postgres This port has installed the following startup scripts which may cause these network services to be started at boot time. /usr/local/etc/rc.d/postgresql If there are vulnerabilities in these programs there may be a security risk to the system. FreeBSD makes no guarantee about the security of ports included in the Ports Collection. Please type 'make deinstall' to deinstall the port if this is a concern. For more information, and contact details about the security status of this software, see the following webpage: ===> Cleaning for postgresql10-client-10.1 ===> Cleaning for postgresql10-server-10.1

Init the new Database

/usr/local/etc/rc.d/postgresql initdb creating directory /var/db/postgres/data11 ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /usr/local/bin/pg_ctl -D /var/db/postgres/data11 -l logfile start

Start PostgreSQL Service

service postgresql start

Recreate Schema Users

create user logik password '.....';
create user vpl password '...';

Restore the Database

su postgres psql -f /usr/local/www/wnk20171230.sql > /tmp/wnk20171230a.log psql -l dbwnk2016 Next: Import nginx accesslog to PostgreSQL UP: TOC