流复制环境中重启主库后报错WAL被removed的解决方法
[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" start
server starting
[postgres@db1 testspace]$ LOG: database system was shut down at 2017-05-31 10:54:57 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
[postgres@db1 testspace]$ ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
LOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
修改postgresql.conf中的参数wal_keep_segments为10(默认为0)
然后reload使参数生效
[postgres@db1 pgdb]$ /usr/local/pgsql/bin/pg_ctl reload -D /home/postgres/testspace/pgdb/
server signaled
LOG: received SIGHUP, reloading configuration files
[postgres@db1 pgdb]$ LOG: parameter "wal_keep_segments" changed to "10"
[postgres@db1 pgdb]$ /usr/local/pgsql/bin/psql -d postgres
psql (9.6.2)
Type "help" for help.
postgres=# show wal_keep_segments;
wal_keep_segments
-------------------
10
(1 row)
[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" stop
waiting for server to shut down...LOG: received fast shutdown request
.LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
FATAL: terminating connection due to administrator command
LOG: shutting down
LOG: database system is shut down
done
server stopped
[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" start
server starting
[postgres@db1 testspace]$ LOG: database system was shut down at 2017-05-31 11:37:46 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
[postgres@db1 testspace]$ /usr/local/pgsql/bin/psql -d postgres
psql (9.6.2)
Type "help" for help.
postgres=#
postgres=# select * from test;
id
----
1
2
3
4
5
6
7
(7 rows)
postgres=# insert into test values (8);
INSERT 0 1
postgres=#
[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" start
server starting
[postgres@db1 testspace]$ LOG: database system was shut down at 2017-05-31 10:54:57 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
[postgres@db1 testspace]$ ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
LOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
修改postgresql.conf中的参数wal_keep_segments为10(默认为0)
然后reload使参数生效
[postgres@db1 pgdb]$ /usr/local/pgsql/bin/pg_ctl reload -D /home/postgres/testspace/pgdb/
server signaled
LOG: received SIGHUP, reloading configuration files
[postgres@db1 pgdb]$ LOG: parameter "wal_keep_segments" changed to "10"
[postgres@db1 pgdb]$ /usr/local/pgsql/bin/psql -d postgres
psql (9.6.2)
Type "help" for help.
postgres=# show wal_keep_segments;
wal_keep_segments
-------------------
10
(1 row)
[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" stop
waiting for server to shut down...LOG: received fast shutdown request
.LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
FATAL: terminating connection due to administrator command
LOG: shutting down
LOG: database system is shut down
done
server stopped
[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" start
server starting
[postgres@db1 testspace]$ LOG: database system was shut down at 2017-05-31 11:37:46 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
[postgres@db1 testspace]$ /usr/local/pgsql/bin/psql -d postgres
psql (9.6.2)
Type "help" for help.
postgres=#
postgres=# select * from test;
id
----
1
2
3
4
5
6
7
(7 rows)
postgres=# insert into test values (8);
INSERT 0 1
postgres=#