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.
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
/usr/local/bin/psql -U postgresql -d dbname -t --command "show server_version"
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 |
/usr/local/bin/pg_dump --create --encoding=UTF8 --column-inserts --dbname=wnk2016 --format=p --inserts --username=postgres --file=/usr/local/www/wnk20181025.sql
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/pgxs.mk
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/
At the time of writing the default version for postgreSQL was version 9.3. However, we need version 10.
vi /usr/ports/Mk/bsd.default-versions.mk
Change the following values
# Possible values: 9.2, 9.3, 9.4, 9.5, 9.6 PGSQL_DEFAULT?= 9.6pkg 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
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:
http://www.postgresql.org/
===> Cleaning for postgresql10-client-10.1
===> Cleaning for postgresql10-server-10.1
/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
service postgresql start
create user logik password '.....';
create user vpl password '...';
su postgres
psql -f /usr/local/www/wnk20171230.sql > /tmp/wnk20171230a.log
psql -l dbwnk2016
Published: May 13, 2019
The information on this page is user generated content. The content does not claim to be complete or correct.
Everybody is invited to add or change the data. Just click on this link. No login or email is required. Thanks.