When I do the next test with postgresql 9.2.21 and pg_rman, the vacuum
command of the table will be hang which has a GIN index.
【create a table with gin index】
postgres@X86U149:~$ psql postgres -p 5432
psql (9.2.21)
Type "help" for help.
postgres=# \d+ tsvector_test Table "public.tsvector_test" Column | Type | Modifiers |
Storage | Stats target | Description
------------+----------+-----------+----------+--------------+-------------id | bigint | | plain
| | phone_list | tsvector | | extended | |
Indexes: "idx_tsvector_test" gin (phone_list)
Has OIDs: no
postgres=# vacuum tsvector_test;
VACUUM
postgres=# \q
【use pg_rman tool to backup and restore】
postgres@X86U149:~$ pg_rman backup -B /home/postgres/backup/ -d postgres -p
5432 -b f
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly
copied.
postgres@X86U149:~$ pg_rman validate -B /home/postgres/backup/
INFO: validate: "2017-06-15 23:10:06" backup and archive log files by CRC
INFO: backup "2017-06-15 23:10:06" is valid
postgres@X86U149:~$ pg_ctl stop
waiting for server to shut down....LOG: received smart shutdown request
LOG: autovacuum launcher shutting down
LOG: shutting down
LOG: database system is shut downdone
server stopped
postgres@X86U149:~$ pg_rman restore -B /home/postgres/backup/
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2017-06-15
23:10:06"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2017-06-15 23:10:06" backup and archive log files by SIZE
INFO: backup "2017-06-15 23:10:06" is valid
INFO: restoring database files from the full mode backup "2017-06-15
23:10:06"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2017-06-15 23:10:06" is valid
INFO: restoring WAL files from backup "2017-06-15 23:10:06"
INFO: restoring online WAL files and server log files
INFO: generating recovery.conf
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is
started.
postgres@X86U149:~$ pg_ctl start
server starting
postgres@X86U149:~$ LOG: database system was interrupted; last known up at
2017-06-15 23:10:07 CST
LOG: starting archive recovery
LOG: restored log file "000000010000000000000010" from archive
LOG: redo starts at 0/10000080
LOG: consistent recovery state reached at 0/100000A8
LOG: restored log file "000000010000000000000011" from archive
cp: cannot stat '/home/postgres/archive//000000010000000000000012': No such
file or directory
LOG: record with zero length at 0/12000080
LOG: redo done at 0/12000020
LOG: last completed transaction was at log time 2017-06-15
23:10:10.481215+08
cp: cannot stat '/home/postgres/archive//00000002.history': No such file or
directory
LOG: selected new timeline ID: 2
cp: cannot stat '/home/postgres/archive//00000001.history': No such file or
directory
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
【connect the database and vacuum the table 】
postgres@X86U149:~$ psql postgres -p 5432
psql (9.2.21)
Type "help" for help.
postgres=# \d+ List of relationsSchema | Name | Type | Owner | Size | Description
--------+---------------+-------+----------+-------+-------------public | phone | table | postgres | 36 MB |
public| tsvector_test | table | postgres | 47 MB |
(2 rows)
postgres=# vacuum tsvector_test;
^Z
[2]+ Stopped psql postgres -p 5432
postgres=# select pid,query from pg_stat_activity; pid | query
-------+-----------------------------------------21633 | vacuum tsvector_test;22885 | select pid,query from
pg_stat_activity;
(2 rows)
However,the vacuum command is hang and donot return result for a long time.
Through the gdb, see the trace is as following
#0 0x00007f955c3e0057 in semop () at ../sysdeps/unix/syscall-template.S:84
#1 0x00000000006034d8 in PGSemaphoreLock ()
#2 0x0000000000647ae8 in LWLockAcquire ()
#3 0x00000000004b2c2b in ginInsertCleanup ()
#4 0x00000000004b1ade in ginvacuumcleanup ()
#5 0x00000000007212f6 in FunctionCall2Coll ()
#6 0x00000000005787cb in lazy_scan_heap ()
#7 0x0000000000579148 in lazy_vacuum_rel ()
#8 0x00000000005769ba in vacuum_rel ()
#9 0x00000000005774e9 in vacuum ()
#10 0x0000000000656100 in PortalRunUtility ()
#11 0x0000000000656cf5 in PortalRunMulti ()
#12 0x0000000000657858 in PortalRun ()
#13 0x00000000006546d6 in PostgresMain ()
#14 0x0000000000610d40 in ServerLoop ()
#15 0x0000000000611996 in PostmasterMain ()
#16 0x0000000000458b3e in main ()
And more, when I use the debug version, this is an assert failuer in the
function processPendingPage
Assert(maxoff >= FirstOffsetNumber);
and the maxoff is 0. Through the PageGetMaxOffsetNumber,I think it read an
empty page.
Whether this is the postgresql or pg_rman problem.
--
View this message in context:
http://www.postgresql-archive.org/GIN-index-vacuum-hang-after-pg-rman-restore-in-PG9-2-21-tp5966975.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
