Hi,
I'm trapped with a problem when trying to create an index on a table with about 2.8 billion records in it.
ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
or
create index pknode on nodes(id);
Postgres terminated with segfault during the proocess.
I'm trying to build index after all data loaded to make the whole process faster, same operation worked on a 300 million records table.
I'm using postgres 9.4.4 with postGIS extension 2.1.8
postgresql.conf:
max_connections = 50# (change requires restart)
shared_buffers = 192000MB
work_mem = 512MB
maintenance_work_mem = 128000MB
dynamic_shared_memory_type = posix
fsync = off
checkpoint_segments = 128
checkpoint_completion_target = 0.9
effective_cache_size = 500GB
default_statistics_target = 500
Hardware:
72 core cpu
755G memory
message from /var/log
2015-07-15T21:40:54.457538-07:00 rd12d02ls-osmdb0002 kernel: postgres[62626]: segfault at 7f31613f103c ip 0000000000785180 sp 00007fffe2a41cb0 error 4 in postgres[400000+561000]
From pg_log:
< 2015-07-15 21:47:12.447 PDT >LOG: server process (PID 62626) was terminated by signal 11: Segmentation fault
< 2015-07-15 21:47:12.447 PDT >DETAIL: Failed process was running: create index pknode on nodes(id);
< 2015-07-15 21:47:12.447 PDT >LOG: terminating any other active server processes
< 2015-07-15 21:47:12.447 PDT >WARNING: terminating connection because of crash of another server process
< 2015-07-15 21:47:12.447 PDT >DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
< 2015-07-15 21:47:12.447 PDT >HINT: In a moment you should be able to reconnect to the database and repeat your command.
< 2015-07-15 21:47:12.501 PDT >LOG: all server processes terminated; reinitializing
< 2015-07-15 21:47:45.917 PDT >LOG: database system was interrupted; last known up at 2015-07-15 21:34:41 PDT
< 2015-07-15 21:47:45.948 PDT >LOG: database system was not properly shut down; automatic recovery in progress
< 2015-07-15 21:47:45.965 PDT >LOG: record with zero length at 1A7/C653FAA8
< 2015-07-15 21:47:45.965 PDT >LOG: redo is not required
< 2015-07-15 21:47:46.783 PDT >LOG: autovacuum launcher started
< 2015-07-15 21:47:46.783 PDT >LOG: database system is ready to accept connections
I'm new to postgres and is really scratching my head don't know how to control the memory the indexing process uses. Would really appreciate it if anyone could point me with some directions, is this a configuration problem I have for my instance?
Thanks
Yiqing