3

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
  • 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

Your Answer

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

Browse other questions tagged or ask your own question.