6

I have a script which runs the following:

pg_restore tmp/latest.backup --verbose --clean --no-acl --no-owner --dbname hub_development --jobs=12

This frequently fails with the following error:

error: could not find block ID 4584 in archive -- possibly due to out-of-order restore request, which cannot be handled due to lack of data offsets in archive

pg_restore: error: a worker process died unexpectedly

This error, in turn, means that tables which should have indices, primary keys, etc. end up not having them. For example, when run without multiple cores, our users table looks like this, as expected:


                                           Table "public.users"
       Column       |            Type             | Collation | Nullable |              Default              
--------------------+-----------------------------+-----------+----------+-----------------------------------
 id                 | integer                     |           | not null | nextval('users_id_seq'::regclass)
 created_at         | timestamp without time zone |           | not null | 
 updated_at         | timestamp without time zone |           | not null | 
 email              | character varying           |           | not null | 
 confirmation_token | character varying(128)      |           |          | 
 name               | character varying           |           | not null | ''::character varying
 user_type          | character varying           |           |          | 
 encrypted_password | character varying(128)      |           |          | 
 remember_token     | character varying(128)      |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "index_users_on_email" btree (email)
    "index_users_on_remember_token" btree (remember_token)
Referenced by:
    TABLE "project_feedback_users" CONSTRAINT "fk_rails_08af49ba47" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "client_reviews" CONSTRAINT "fk_rails_8fc606dbea" FOREIGN KEY (user_id) REFERENCES users(id)

When run with multiple cores, the table looks like this:


hub_development=# \d users
                                            Table "public.users"
       Column       |            Type             | Collation | Nullable |              Default              
--------------------+-----------------------------+-----------+----------+-----------------------------------
 id                 | integer                     |           | not null | nextval('users_id_seq'::regclass)
 created_at         | timestamp without time zone |           | not null | 
 updated_at         | timestamp without time zone |           | not null | 
 email              | character varying           |           | not null | 
 confirmation_token | character varying(128)      |           |          | 
 name               | character varying           |           | not null | ''::character varying
 user_type          | character varying           |           |          | 
 encrypted_password | character varying(128)      |           |          | 
 remember_token     | character varying(128)      |           |          | 

Based on this, I reached the conclusion that the same worker which created the table itself was not also responsible for adding the indices and foreign keys, that the 2nd worker was attempting to run before the first worker, and that this caused the errors I observed.

The script works fine when I remove the --jobs=12 flag, and the worst-case fix is that I simply do that.

However, for my own education, I'd love to know whether there's a solution which preserves our ability to use multiple cores to parallelize the DB restore while avoiding the out-of-order restore requests.

The error mentions that out-of-order restore requests can't be handled due to lack of data offsets in the archive. Would adding those data offsets solve the problem in the way I described? If so, how would I go about that, and are there any disadvantages to doing so?

I'm not a DB admin and my knowlege here is limited, so please let me know if I haven't provided enough information to answer the question.

My local version of Postgres is 12.1, and the data is coming from a Rails app hosted on Heroku. Here's the result of heroku pg:info:

=== HEROKU_POSTGRESQL_BRONZE_URL, DATABASE_URL
Plan:                  Standard 0
Status:                Available
Data Size:             3.38 GB
Tables:                44
PG Version:            11.5
Connections:           22/120
Connection Pooling:    Available
Credentials:           1
Fork/Follow:           Available
Rollback:              earliest from 2020-01-10 18:17 UTC
Created:               2019-10-29 18:20 UTC
Region:                us
Data Encryption:       In Use
Continuous Protection: On
Maintenance:           not required
Maintenance window:    Wednesdays 18:00 to 22:00 UTC
Add-on:                postgresql-metric-02684
| improve this question | | | | |
  • This looks like a bug somewhere. If you can reproduce it with normal PostgreSQL, you could report it. Otherwise, you'll have to ask Heroku. – Laurenz Albe Jan 16 at 20:55
  • I'm running into the same problem with a local restore from a backup of our AWS RDS instance. I didn't have this problem when I was running 11.2 locally. Now, running 12.1, I hit this every time I try to restore from a dump. Didn't realize it might be connected to parallelism, so thanks for that tidbit! Would love to learn more about this as well. – nronnei Jan 27 at 22:15
2

If pg_dump can't seek in the output file (typically meaning the output was piped somewhere) it can't update the output file with certain metadata and silently leaves it out of the dump. Changes to the pg_restore -j logic in PostgreSQL 12 made it depend on that data and when it's not available you get the possibly due to out-of-order restore request error.

If you can run your pg_dump with the -f flag to specify a file for output you'll get a dump file with the correct metadata. If you don't have control over how pg_dump was run to make that change you won't be able to do a parallel restore in PostgreSQL 12+.

I've submitted a patch upstream to document some of this behavior in the relevant places so hopefully it isn't as much of a mystery to people going forward. The extra metadata that is only written if your output file supports seeking is not documented anywhere other than comments in the pg_dump code so it is a bit of a mystery for most users.

| improve this answer | | | | |
0

I had this issue. For me the solution was to use an older version of postgres in development. Our production server was using 9.x or 10.x but I was trying to restore using postgres 12.x. Downgrading to 10.x worked for me.

| improve this answer | | | | |
0

At first blush, it looks like a file system issue where pg_dump reached EOF.

See https://github.com/postgres/postgres/blob/7a9c9ce6411720c2bbeaf6e64855d4263c47ea80/src/bin/pg_dump/pg_backup_custom.c#L460

But you say it works fine without --jobs=12. Then this looks like a synchronization issue, but that should show up a lot more often with other people reporting. (I would experiment with --jobs=2.)

My guess then it has to do with the OS. Maybe maximum open files has been hit (normally I would look in /var/log/messages, but you are on Heroku). For more detail on max open files see: https://www.postgresql.org/docs/12/kernel-resources.html#id-1.6.5.6.5

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.