Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the method of postgreSQL11 backup and recovery". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Archive directory:

[postgres@centos1 arch] $pwd/home/postgres/arch

2. Set up archiving commands:

Archive_command- -DATE= `date +% Y% m% d` DIR= "/ home/postgres/arch/$DATE"; (test-d $DIR | | mkdir-p $DIR) & & cp% p $DIR/%f

To modify wal_level and archive_mode parameters, you need to restart the database to take effect. To modify archive_command, you do not need to restart, you only need reload:

Postgres=# SELECT pg_reload_conf ()

3. Verify archiving:

Postgres=# checkpointpostgres-#; CHECKPOINTpostgres=# select pg_switch_wal (); pg_switch_wal-0/11029F08 (1 row) [postgres@centos1 20200103] $lltotal 16M Jan-1 postgres postgres 16m Jan 3 10:45 0000000100000000011

4. Configure backup user access:

[postgres@centos1 pg_root] $vi pg_hba.confhost replication rep 0.0.0.0 Compact 0 md5

5. Create a basic backup:

[postgres@centos1 pgbak] $pg_basebackup-Ft-D / home/postgres/ pgbak`date +% F`-h 192.168.1.212-p 1921-U repPassword: [postgres@centos1 pgbak2020-01-03] $lltotal 96M postgres postgres RW-1 postgres postgres 1.5K Jan 3 11:34 26097.tarr RW-1 postgres postgres 80m Jan 3 11:34 base.tar-rw- 1 postgres postgres 17M Jan 3 11:34 pg_wal.tar

View the contents of the backup:

[postgres@centos1 pgbak2020-01-03] $tar-tvf base.tar | less-rw- postgres/postgres 2020-01-03 11:34 backup_label-rw- postgres/postgres 28 2020-01-03 11:34 tablespace_mapdrwx- postgres/postgres 0 2020-01-03 11:34 pg_wal/drwx- postgres/postgres 0 2020-01-03 11:34. / pg_wal/archive_status/drwx -postgres/postgres 0 2019-12-19 17:24 global/-rw- postgres/postgres 16384 2019-12-17 16:42 global/1262-rw- postgres/postgres 49152 2019-06-17 23:47 global/1262_fsm-rw- postgres/postgres 0 2019-06-17 23:47 global/2964-rw- postgres/postgres 16384 2019-01-03 10:45 global/ 1213 global/1136_fsm-rw- postgres/postgres RW-postgres/postgres 49152 2019-06-17 23:47 global/1213_fsm-rw- postgres/postgres 16384 2019-06-17 23:47 global/1136-rw- postgres/postgres 49152 2019-06-17 23:47 global/1136_fsm-rw- postgres/postgres 16384 2019-12-17 11:49 global/1260

6. Generate test recovery data:

Postgres=# create table test_bk (id int) tablespace tbs_pg01;CREATE TABLEpostgres=# insert into test_bk values (1), (2); INSERT 0 2

Since WAL files are archived only when they are full of 16MB, there may be very few writes during the test phase, so you can manually switch over the WAL after performing a basic backup. Such as:

Postgres=# checkpoint;CHECKPOINTpostgres=# select pg_switch_wal (); pg_switch_wal-0/14027F78 (1 row)

7. Restore part

Shut down the database:

[postgres@centos1 ~] $pg_ctl stopwaiting for server to shut down.... Doneserver stopped [postgres@centos1 ~] $ipcs

Remove databases and tablespaces

[postgres@centos1 ~] $mv pgdata pgdatatbbk [postgres@centos1 ~] $mv pg_root pg_rootbk

Copy the backup file to the original directory

[postgres@centos1 ~] $echo $PGDATA/home/postgres/pg_ root [Postgres @ centos1 ~] $mkdir pg_ root [Postgres @ centos1 ~] $mkdir pgdata [postgres@centos1 ~] $cd pgbak2020-01-03 [postgres@centos1 pgbak2020-01-03] $lltotal 96M postgres postgres 1.5K Jan 3 11:34 26097.Tarmurrw-1 postgres postgres 80m Jan 3 11:34 base.tar-rw- 1 postgres postgres 17m Jan 3 11:34 pg_ wal.tars [Postgres @ centos1 pgbak2020-01-03] $cp 26097.tar / home/postgres/pgdata [postgres@centos1 pgbak2020-01-03] $cp base.tar $PGDATA [postgres@centos1 pgbak2020-01-03] $cp pg_wal.tar $PGDATA [postgres@centos1 pgbak2020-01-03] $cd $PGDATA [postgres@centos1 pg_root] $lltotal 96Mmurrw-1 postgres postgres 80m Jan 3 12:06 base.tar-rw- 1 Postgres postgres 17M Jan 3 12:07 pg_wal.tar

Extract the base:

[postgres@centos1 pg_root] $tar-xvf base.tar

Extract the tablespace:

[postgres@centos1 pgdata] $tar-xvf 26097.tar PG_11_201809051/ [postgres@centos1 pgdata] $lltotal 4.0K RW-1 postgres postgres 1.5K Jan 3 12:06 26097.tardrwx-2 postgres postgres 6 Jan 2 20:07 PG_11_201809051

Extract the archive file:

[postgres@centos1 pg_root] $tar-xvf pg_wal.tar 000000010000000000000013archive_status/000000010000000000000013.done

Copy recovery Fil

[postgres@centos1 pg_root] $cp / opt/postgresql/share/recovery.conf.sample recovery.conf configuration recovery file command: vi recovery.confrestore_command ='cp / home/postgres/arch/20200103/%f% p'

Start the database:

[postgres@centos1 pg_root] $pg_ctl start

Waiting for server to start....2020-01-03 13 FATAL 0515 FATAL 16.488 CST [21872] FATAL: data directory "/ home/postgres/pg_root" has invalid permissions

2020-01-03 13 or 05V CST [21872] DETAIL: Permissions should be u=rwx (0700) or upright rwx GRX (0750).

Stopped waiting

Pg_ctl: could not start server

Examine the log output.

If an error is reported, modify the permission:

[postgres@centos1] $chmod-R 750. / pg_root

Start the database:

[postgres@centos1 ~] $pg_ctl start waiting for server to start....2020-01-03 13 CST 16.927 CST [22152] LOG: listening on IPv4 address "0.0.0.0", port 19212020-01-03 13 CST 0972 CST [22152] LOG: listening on Unix socket "/ tmp/.s.PGSQL.1921" 2020-01-03 1315 CST [22153] LOG: database system was interrupted Last known up at 2020-01-03 11:34:44 CST2020-01-03 13 CST 17.035 CST [22153] LOG: creating missing WAL directory "pg_wal/archive_status" 2020-01-03 13 CST 17.446 CST [22153] LOG: starting archive recovery2020-01-03 13 CST 17.446 CST [22153] LOG: restored log file "0000000100000000000013" from archive2020-01-03 13 CST 09LOG [22153] LOG: redo starts at 01300282020-01 -03 13 consistent recovery state reached at 09 CST 17.726 CST [22153] LOG: consistent recovery state reached at 00001302020-01-03 1309V 17.727 CST [22152] LOG: database system is ready to accept read only connections2020-01-03 1309V 17.743 CST [22153] LOG: restored log file "0000000100000000000014" from archive doneserver started [postgres@centos1 ~] $2020-01-03 13R 0920 CST [22153] LOG: restored log file "0000000100000000000015" from archivecp: cannot stat'/ home/ Postgres/arch/20200103/000000010000000000000016': No such file or directory2020-01-03 13 LOG 09LOG: redo done at 00001402020-01-03 13 CST 18.085 CST: LOG: last completed transaction was at log time 2020-01-03 1140 CST 52.26971mm 082020-01-03 1309pur18.125 CST [22153] LOG: restored log file "00000001000000000015" from archivecp: cannot stat'/ home/postgres/arch/20200103/00000002.history': No Such file or directory2020-01-03 13 LOG CST [22153] LOG: selected new timeline ID: 22020-01-03 13 purl 18.09Rd 18.477 CST [22153] LOG: archive recovery completecp: cannot stat'/ home/postgres/arch/20200103/00000001.history': No such file or directory2020-01-03 13 Vera 09Fringe 18.840 CST [22152] LOG: database system is ready to accept connections

Start up to see if the table exists:

[postgres@centos1] $psqlpsql (11.3) Type "help" for help.pgdb=#\ c postgresYou are now connected to database "postgres" as user "postgres" .Postgres = # select * from test_bk; id-1 2

When the recovery is complete, the recovery file will become .done

-rwxr-x--- 1 postgres postgres 5.7K Jan 3 13:00 recovery.done

By the way, remember the logical backup part. Hey, hey.

Logical backup

[postgres@centos1 dump] $pg_dump-F c-f. / pgdb.dmp-C-E UTF8-h 192.168.1.212-p 1921-U postgres-d pgdb

View backup files

[postgres@centos1 dump] $pg_restore-l. / pgdb.dmp, "what are postgreSQL11 backup and recovery methods"? that's it. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.