pg_dump on Database throwing error 'out of shared memory'

Question

Getting problem when taking backup on database contains around 50 schema with each schema having around 100 tables.

pg_dump throwing below error suggesting that to increase max_locks_per_transaction.

pg_dump: WARNING:  out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
pg_dump: The command was: SELECT tableoid, oid, prsname, prsnamespace, prsstart::oid, prstoken::oid, prsend::oid, prsheadline::oid, prslextype::oid FROM pg_ts_parser

An updated of max_locks_per_transaction to 256 in postgresql.conf did not solve the problem.

Are there any possibilities which can cause this problem?

Edited:(07 May, 2016)

Postgresql version = 9.1

Operating system = Ubuntu 14.04.2 LTS

shared_buffers in postgresql.conf = 2GB

Edited:(09 May, 2016)

My postgres.conf

maintenance_work_mem = 640MB
wal_buffers = 64MB
shared_buffers = 2GB
max_connections = 100
max_locks_per_transaction=10000

score 8 · Answer 1 · answered May 06 '16 at 18:54

8

You might need to increase max_locks_per_transaction even further. Check the documentation for details on what this parameter means. If in doubt, check how many tables you have and set max_locks_per_transaction to at least that much, then you should be OK.

answered May 06 '16 at 18:54

Peter Eisentraut

35,221
12
85
90

Details? How many tables do you have, what are your configuration settings? – Peter Eisentraut May 07 '16 at 17:14
@PeterEisentraut, thanks for the tip. Initially not sure in which file that property has to be modified. Later got help from friends where to modify. `../data/postgresql.conf` – Rao Mar 03 '17 at 06:00
@Rao: And? Did it help? – Sander Toonen Dec 09 '19 at 06:57

score 8 · Accepted Answer · edited Mar 29 '18 at 00:41

8

I solved this problem by taking backup for all schema individually as size of database (be it no.of schemas or no.of tables) increases it's hard to take backup using pg_dump.

I have done following modification to the script to take schema-wise backup:

Before running pg_dump, list all database schemas into a file. So that we can iterate all schemas and take backup for a schema.

Here is the command to list all schema to a file

psql <db_name> -o <output_file> < <sql_to_list_schema>

Here sql_to_list_schema contains

SELECT n.nspname FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema';
Now read all lines of output_file and take backup of that schema

pg_dump <db_name> -f <backup_file> -i -x -O -R -n <schema_name_read_from_file>

edited Mar 29 '18 at 00:41

Nicholas Sizer

3,490
3
26
29

answered May 07 '16 at 13:30

pgollangi

848
3
13
28

The [link](http://www.wisdombay.com/articles/article000013.htm) you provided for the script, doesn't work anymore... – Roshana Pitigala Mar 28 '18 at 16:34
You might want to use some extra params with to the `psql` command. This will easier reading of the names to a variable like: `--no-align --tuples-only --record-separator=" "`. – Nux Oct 10 '18 at 14:59

score -1 · Answer 3 · edited Jun 15 '20 at 10:55

-1

Try this

shared_buffers = 512GB
max_locks_per_transaction=10000

Please note that every env is different

edited Jun 15 '20 at 10:55

Arseniy-II

8,323
5
24
49

answered Jun 15 '20 at 09:03

zdarova

97
5

I get this error: HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 563431931904 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. – Daniel Bentes Oct 20 '21 at 18:18

score -3 · Answer 4 · answered Jul 13 '21 at 15:09

-3

I tried using the below without tweaking any buffer size and it worked.

psql -U <db_user_name> -d <db_name> -f <file_name>.sql

answered Jul 13 '21 at 15:09

Raj Rajeshwar Singh Rathore

673
1
9
30

pg_dump on Database throwing error 'out of shared memory'

4 Answers4

Linked