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

We know that pg_upgrade and pg_dump/pg_restore can upgrade databases in large versions, so how do small versions upgrade, for example, from 9.6.3 to 9.6.5?

Principle: start with a new version of the software program and specify the old data directory

Install pgsql9.6.3

Mkdir-p / usr/local/pgsql/pgsql9.6.3/ {data,arch}

Groupadd dba

Useradd-g dba-G root postgres-d / usr/local/pgsql

Tar xf postgresql-9.6.3.tar.gz

Chmod-R 755 / usr/local/pgsql

Chown-R postgres:dba / usr/local/pgsql

Chmod-R 700 / usr/local/pgsql/pgsql9.6.3/data/

. / configure-- prefix=/usr/local/pgsql/pgsql9.6.3/

Make world & & make install-world

Cd / usr/local/pgsql/pgsql9.6.3/bin

. / initdb-D / usr/local/pgsql/pgsql9.6.3/data-U postgres-E UTF8-W

Vim pg_hba.conf

Host all all 0.0.0.0/0 md5

Vim postgresql.conf

Listen_addresses ='*'

Wal_level = replica

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.6.5/arch/%f & & cp% p / usr/local/pgsql/pgsql9.6.5/arch/%f'

/ usr/local/pgsql/pgsql9.6.3/bin/pg_ctl start-D / usr/local/pgsql/pgsql9.6.3/data

# generate test data

Create database testdb1

\ C testdb1

Create table T1 (id int primary key, info text)

Insert into T1 select generate_series (1 million), 'helloWorld'

Install pgsql9.6.5

Mkdir-p / usr/local/pgsql/pgsql9.6.5/ {data,arch}

Groupadd dba

Useradd-g dba-G root postgres-d / usr/local/pgsql

Tar xf postgresql-9.6.5.tar.gz

Chmod-R 755 / usr/local/pgsql

Chown-R postgres:dba / usr/local/pgsql

Chmod-R 700 / usr/local/pgsql/pgsql9.6.5/data/

. / configure-- prefix=/usr/local/pgsql/pgsql9.6.5/

Make world & & make install-world

Cd / usr/local/pgsql/pgsql9.6.5/bin

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

Vim pg_hba.conf

Host all all 0.0.0.0/0 md5

Vim postgresql.conf

Listen_addresses ='*'

Wal_level = replica

Port = 5431

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'

/ usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start-D / usr/local/pgsql/pgsql9.6.5/data

/ usr/local/pgsql/pgsql9.6.5/bin/pg_ctl stop-m fast-D / usr/local/pgsql/pgsql9.6.5/data

# stop the old database 9.6.3

/ usr/local/pgsql/pgsql9.6.3/bin/pg_ctl stop-m fast-D / usr/local/pgsql/pgsql9.6.3/data

# start with the software program of 9.6.5 and specify the data directory of 9.6.3

/ usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start-D / usr/local/pgsql/pgsql9.6.3/data

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=# select count (*) from T1

Count

-

1000000

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.