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 14 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> postgresql13-client-13.4 PostgreSQL database (client) postgresql13-server-13.4_1 PostgreSQL is the most advanced open-source database available anywhere </pre> <h3>What PostgreSQL Databases are in Use?</h3> <code>psql -U postgres -l</code> <samp> List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+---------+----------------------- mydbname | postgres | UTF8 | C | C.UTF-8 | postgres | postgres | UTF8 | C | C.UTF-8 | template0 | postgres | UTF8 | C | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (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=mydbname \</code><br> <code> --format=p --inserts --username=postgres \</code><br> <code> --file=/usr/local/www/mydbname20211119.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 101152768 Nov 20 11:57 /usr/local/www/mydbname20211119.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' '--without-gssapi' '--enable-nls' '--without-pam' '--with-openssl' '--prefix=/usr/local' '--localstatedir=/var' '--mandir=/usr/local/man' '--infodir=/usr/local/share/info/' '--build=amd64-portbld-freebsd13.0' 'build_alias=amd64-portbld-freebsd13.0' 'CC=cc' 'CFLAGS=-O2 -pipe -fstack-protector-strong -fno-strict-aliasing ' 'LDFLAGS= -L/usr/local/lib -lpthread -L/usr/local/lib -fstack-protector-strong ' 'LIBS=' 'CPPFLAGS=-I/usr/local/include' 'CXX=c++' 'CXXFLAGS=-O2 -pipe -fstack-protector-strong -fno-strict-aliasing ' '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 -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -pipe -fstack-protector-strong -fno-strict-aliasing CFLAGS_SL = -fPIC -DPIC LDFLAGS = -L/usr/local/lib -lpthread -L/usr/local/lib -fstack-protector-strong -L/usr/local/lib -Wl,--as-needed -Wl,-R'/usr/local/lib' LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lintl -lssl -lcrypto -lz -lreadline -lexecinfo -lm VERSION = PostgreSQL 13.4 </samp> <h3>Update PostgreSQL default version in FreeBSD config files</h3> <p>At the time of writing the default version for PostgreSQL was version 13. However, we need version 14.</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?= 14 </samp> <h3>Stop PostgreSQL service and delete binaries</h3> <code>service postgresql stop</code> <p>Delete the PostgreSQL Packages</p> <code>pkg delete -fy postgresql13-server-13.4_1 </code> <code>pkg delete -fy postgresql13-client </code> <h3>Install PostgreSQL 14</h3> <p>The installation of the PostgreSQL server software also installs the PostgreSQL client</p> <code> pkg install postgresql14-server </code> <p>Let's start the database</p> <code> /usr/local/etc/rc.d/postgresql initdb The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locales COLLATE: C CTYPE: C.UTF-8 MESSAGES: C.UTF-8 MONETARY: C.UTF-8 NUMERIC: C.UTF-8 TIME: C.UTF-8 The default text search configuration will be set to "english". Data page checksums are disabled. creating directory /var/db/postgres/data14 ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: 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/data14 -l logfile start </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 exit </code> <code> psql -U postgres -f /usr/local/www/mydbname20211119.sql > /var/log/mydbnameinstall20211119.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/data13.</p> </p>
URL:
Operation:	Delete Update Insert
Template Prompt