Title:
Tag1 for Path:	a single word in lower characters
Tag2 for Path:
List of alternative single tags:	one or many words in lower characters, separated by comma or space
Text:	<p>The aim of this document is to upgrade to PostgreSQL 11 on FreeBSD with minimal downtime.</p> <b>First take a snapshot of the virtual machine or save the entire server.</b> <h3>First get some information about the environment.</h3> <h3>What Packages are related to SQL?</h3> <code>pkg info | grep sql</code> <pre> php71-pdo_pgsql-7.1.20 The pdo_pgsql shared extension for php php71-pgsql-7.1.20 The pgsql shared extension for php postgresql10-client-10.6 PostgreSQL database (client) postgresql10-contrib-10.6 The contrib utilities from the PostgreSQL distribution postgresql10-server-10.6 PostgreSQL is the most advanced open-source database available anywhere </pre> <h3>What PostgreSQL Databases are in Use?</h3> <code>psql -U postgres -d mydbname -l</code> <samp> List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+-------+----------+---------+-------+------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/pgsql + template1 | postgres | UTF8 | C | C | =c/pgsql + mydbname | postgres | UTF8 | C | C | (4 rows) </samp> <p>In our case we only need to upgrade database "mydbname".</p> <h3>Make a Dump of the whole PostgreSQL Databases</h3> <code>/usr/local/bin/pg_dump --create --encoding=UTF8 --column-inserts --dbname=nydbname \</code><br> <code> --format=p --inserts --username=postgres \</code><br> <code> --file=/usr/local/www/mydbname20181207.sql</code><br> <p>Check size of the exported data and ddl</p> <code>ls -altr /usr/local/www/*.sql</code> <samp> -rw-r--r-- 1 root wheel 132061170 Dec 7 18:18 /usr/local/www/mydbname20181207.sql </samp> <h3>List Current PostgreSQL Configuration</h3> <code>pg_config</code> <p>Just in case....</p> <samp> 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/share/info/' '--build=amd64-portbld-freebsd11.2' 'build_alias=amd64-portbld-freebsd11.2' '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' 'PKG_CONFIG=pkgconf' 'LDFLAGS_SL=' CC = cc CPPFLAGS = -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/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 VERSION = PostgreSQL 10.6 </samp> <h3>Update PostgreSQL default version in FreeBSD config files</h3> <p>At the time of writing the default version for PostgreSQL was version 10. However, we need version 11.</p> <code>vi /usr/ports/Mk/bsd.default-versions.mk</code> <p>Change the following values</p> <samp> # Possible values: 9.2, 9.3, 9.4, 9.5, 9.6, 10, 11 PGSQL_DEFAULT?= 11 </samp> <h3>Stop PostgreSQL service and delete binaries</h3> <code>service postgresql stop</code> <p>Delete the PostgreSQL Packages</p> <code>pkg delete -fy postgresql10-client-10.6_1;</code> <code>pkg delete -fy postgresql10-contrib-10.6_1;</code> <code>pkg delete -fy postgresql10-server-10.6_1;</code> <h3>Install PostgreSQL 11</h3> <p>The installation of the PostgreSQL server software also installs the PostgreSQL client</p> <code> cd /usr/ports/databases/postgresql11-server/ && make install clean </code> <p>Let's start the database</p> <code> service postgresql start </code> <samp> LOG: ending log output to stderr HINT: Future log output will go to log destination "syslog". </samp> <h2>Restore the exported databases</h2> <code> su postgres psql </code><br> <code> create user abc-user password '???'; create user abcd-user password '???'; \q </code> <code> psql -f /usr/local/www/mydbname20181207.sql > /var/log/mydbnameinstall20181207.log </code><br> <code> psql -l mydbname </code> <h3>Reboot Virtual Machine</h3> <code> shutdown -r now </code> <h3>Cleanup</h3> <p>After successful upgrade you should delete the snapshot of the virtual machine and delete the files in /var/db/postgres/data10.</p>
URL:
Operation:	Delete Update Insert
Template Prompt