[BUGS] GIN index vacuum hang after pg_rman restore in PG9.2.21 - Mailing list pgsql-bugs

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