Shulou(Shulou.com)06/01 Report--

Pg_upgrade

(1) PostgreSQL provides a tool for upgrading large versions, for example, from 9.1 to 9.2, and it can also span multiple large versions at a time, directly from 9.1 to 9.5, etc. Its advantage is that it does not need to import and export data, which is very convenient when the amount of data is relatively large.

(2) it is not suitable for small version upgrades, such as 9.0.1 to 9.0.4

(3) pg_upgrade cannot be used until after PostgreSQL8.4.x.

(4) if the amount of data is small, you can use pg_dump/pg_restore to upgrade the database.

Environment

Old:postgresql-9.5.9

New:postgresql-9.6.5

Install postgresql-9.5.9

[root@Darren2 ~] # mkdir-p / usr/local/pgsql/pgsql9.5.9/ {data,arch}

[root@Darren2 pgsql] # groupadd dba

[root@Darren2 pgsql] # useradd-g dba-G root postgres-d / usr/local/pgsql

[root@Darren2 pgsql9.5.9] # tar xf postgresql-9.5.9.tar.gz

[root@Darren2 pgsql] # chmod-R 755 / usr/local/pgsql

[root@Darren2 pgsql] # chown-R postgres:dba / usr/local/pgsql

[root@Darren2 pgsql] # chmod-R 700 / usr/local/pgsql/pgsql9.5.9/data/

Darren2:postgres:/usr/local/pgsql: > cat .bash _ profile

# .bash _ profile

# Get the aliases and functions

If [- f ~ / .bashrc]; then

. ~ / .bashrc

Fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

Export PGHOME=/usr/local/pgsql/pgsql9.5.9

Export PGDATA=$PGHOME/data

Export PATH=$PATH:$PGHOME/bin

# stty erase

Set umask to 022

Umask 022

PS1= `uname-n` ":"'$USER' ":"'$PWD' ": >"; export PS1

[root@Darren2 postgresql-9.5.9] #. / configure-prefix=/usr/local/pgsql/pgsql9.5.9/

[root@Darren2 postgresql-9.5.9] # make world & & make install-world

Darren2:postgres:/usr/local/pgsql: > initdb-D $PGDATA-U postgres-E UTF8-W

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data: > vim pg_hba.conf

Host all all 0.0.0.0/0 md5

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data: > vim postgresql.conf

Listen_addresses ='*'

Wal_level = archive

Port = 5432

Max_connections = 300

Shared_buffers = 128MB

Logging_collector = on

Log_directory = 'pg_log'

Log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Archive_mode = on

Archive_command = 'test!-f / usr/local/pgsql/pgsql9.5.9/arch/%f & & cp% p / usr/local/pgsql/pgsql9.5.9/arch/%f'

# start the database

Darren2:postgres:/usr/local/pgsql: > pg_ctl start

# create test data

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9: > cd / usr/local/pgsql/pgsql9.5.9

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9: > mkdir tbs1

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data: > psql

Postgres=# create role cdhu1 login encrypted password '147258'

Postgres=# create tablespace tbs1 location'/ usr/local/pgsql/pgsql9.5.9/tbs1'

Postgres=# create database testdb1 template template0 encoding 'UTF8' tablespace tbs1

Postgres=# grant all on database testdb1 to cdhu1

Postgres=# grant all on tablespace tbs1 to cdhu1

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data: > psql-d testdb1-U cdhu1

Testdb1= > create table T1 (id int primary key, info text)

Testdb1= > insert into T1 select generate_series (1pm 2000000), 'helloWorld'

Install a new version of postgresql-9.6.5

[root@Darren2 ~] # mkdir-p / usr/local/pgsql/pgsql9.6.5/ {data,arch}

[root@Darren2 pgsql9.6.5] # tar xf postgresql-9.6.5.tar.gz

[root@Darren2 pgsql] # chmod-R 755 / usr/local/pgsql

[root@Darren2 pgsql] # chown-R postgres:dba / usr/local/pgsql

[root@Darren2 pgsql] # chmod-R 700 / usr/local/pgsql/pgsql9.6.5/data/

[root@Darren2 postgresql-9.5.9] #. / configure-prefix=/usr/local/pgsql/pgsql9.6.5/

[root@Darren2 postgresql-9.5.9] # make world & & make install-world

Darren2:postgres:/usr/local/pgsql/pgsql9.6.5/bin: > cd / usr/local/pgsql/pgsql9.6.5/bin

Darren2:postgres:/usr/local/pgsql: >. / initdb-D / usr/local/pgsql/pgsql9.6.5/data-U postgres-E UTF8-W

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data: > vim pg_hba.conf

Host all all 0.0.0.0/0 md5

Darren2:postgres:/usr/local/pgsql/pgsql9.6.5/data: > vim postgresql.conf

Listen_addresses ='*'

Wal_level = replica

Port = 5431 # must be different from the port number above

Max_connections = 300

Shared_buffers = 128MB

Logging_collector = on

Log_directory = 'pg_log'

Log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Archive_mode = on

Archive_command = 'test!-f / usr/local/pgsql/pgsql9.6.5/arch/%f & & cp% p / usr/local/pgsql/pgsql9.6.5/arch/%f'

# start database service for multiple instances

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.5.9/bin/pg_ctl start-D / usr/local/pgsql/pgsql9.5.9/data/

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start-D / usr/local/pgsql/pgsql9.6.5/data

# stop data service for multiple instances (database service needs to be stopped before upgrade)

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.5.9/bin/pg_ctl stop-m fast-D / usr/local/pgsql/pgsql9.5.9/data

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.6.5/bin/pg_ctl stop-m fast-D / usr/local/pgsql/pgsql9.6.5/data

Analysis of pg_upgrade parameters

-b,-- old-bindir=BINDIR old cluster executable directory

-B,-- new-bindir=BINDIR new cluster executable directory

-d,-- old-datadir=DATADIR old cluster data directory

-D,-- new-datadir=DATADIR new cluster data directory

-p,-- old-port=PORT old cluster port number (default 50432)

-P,-- new-port=PORT new cluster port number (default 50432)

-c,-- check check clusters only, don't change any data

-J,-- jobs number of simultaneous processes or threads to use

-k,-- link link instead of copying files to new cluster

-r,-- retain retain SQL and log files after success

-U,-username=NAME cluster superuser (default "postgres")

-v,-- verbose enable verbose internal logging

There are two ways to upgrade pg_upgrade, one is by default by copying data files to the new data directory, and the other is by creating hard links. The copy method is slow to upgrade, but the original library is still available; the hard link method is faster, but the original library is not available.

Use 9.6.5 pg_upgrade to check for compatibility

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.6.5/bin/pg_upgrade-b / usr/local/pgsql/pgsql9.5.9/bin-B / usr/local/pgsql/pgsql9.6.5/bin-d / usr/local/pgsql/pgsql9.5.9/data-D / usr/local/pgsql/pgsql9.6.5/data-p 5432-P 5431-U postgres-j 8-k-c

Performing Consistency Checks

-

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for reg* system OID user data types ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for roles starting with 'pg_' ok

Checking for presence of required libraries ok

Checking database user is the install user ok

Checking for prepared transactions ok

Formal upgrade

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.6.5/bin/pg_upgrade-b / usr/local/pgsql/pgsql9.5.9/bin-B / usr/local/pgsql/pgsql9.6.5/bin-d / usr/local/pgsql/pgsql9.5.9/data-D / usr/local/pgsql/pgsql9.6.5/data-p 5432-P 5431-U postgres-j 8-k-r-v

.

Upgrade Complete

-

Optimizer statistics are not transferred by pg_upgrade so

Once you start the new server, consider running:

. / analyze_new_cluster.sh

Running this script will delete the old cluster's data files:

. / delete_old_cluster.sh

# generate 2 scripts to collect statistics and delete old clusters, and some log information about the upgrade process

Darren2:postgres:/usr/local/pgsql: > ls-ltr

-rw-r--r--. 1 postgres dba 2215 Oct 18 01:37 pg_upgrade_dump_globals.sql

-rw-. 1 postgres dba 2237 Oct 18 01:37 pg_upgrade_dump_13241.custom

-rw-. 1 postgres dba 2228 Oct 18 01:37 pg_upgrade_dump_1.custom

-rw-. 1 postgres dba 3733 Oct 18 01:37 pg_upgrade_dump_16386.custom

-rw-. 1 postgres dba 2422 Oct 18 01:37 pg_upgrade_dump_13241.log

-rw-. 1 postgres dba 2409 Oct 18 01:37 pg_upgrade_dump_1.log

-rw-. 1 postgres dba 2686 Oct 18 01:37 pg_upgrade_dump_16386.log

-rw-. 1 postgres dba 240810 Oct 18 01:37 pg_upgrade_utility.log

-rwx-. 1 postgres dba 112 Oct 18 01:37 delete_old_cluster.sh

-rwx-. 1 postgres dba 809 Oct 18 01:37 analyze_new_cluster.sh

-rw-. 1 postgres dba 2956 Oct 18 01:37 pg_upgrade_server.log

-rw-. 1 postgres dba 42227 Oct 18 01:37 pg_upgrade_internal.log

# start a new database cluster

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start-D / usr/local/pgsql/pgsql9.6.5/data

# perform statistics collection according to script analyze_new_cluster.sh

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.6.5/bin/vacuumdb-U 'postgres'-- all-- analyze-in-stages-p 5431

Vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)

Vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)

Vacuumdb: processing database "testdb1": Generating minimal optimizer statistics (1 target)

Vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)

Vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)

Vacuumdb: processing database "testdb1": Generating medium optimizer statistics (10 targets)

Vacuumdb: processing database "postgres": Generating default (full) optimizer statistics

Vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Vacuumdb: processing database "testdb1": Generating default (full) optimizer statistics

# check whether the data exists

Darren2:postgres:/usr/local/pgsql: > psql-p 5431

Postgres=# select version ()

Version

-

PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

Testdb1=#\ dt+

List of relations

Schema | Name | Type | Owner | Size | Description

-+-

Public | T1 | table | cdhu1 | 85 MB |

# Delete old data through the above script

Darren2:postgres:/usr/local/pgsql: > cat delete_old_cluster.sh

#! / bin/sh

Rm-rf'/ usr/local/pgsql/pgsql9.5.9/data'

Rm-rf'/ usr/local/pgsql/pgsql9.5.9/tbs1/PG_9.5_201510051'

Darren2:postgres:/usr/local/pgsql: > bash delete_old_cluster.sh

Finally, modify the environment variables and port numbers to be consistent with the original version.

Error

Darren2:postgres:/usr/local/pgsql: > / usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start-D / usr/local/pgsql/pgsql9.6.5/data

Pg_ctl: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory

Solution:

[root@Darren2 postgresql-9.6.5] # ln-s / usr/local/pgsql/pgsql9.6.5/lib/libpq.so.5 / usr/lib64/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.