Postgresql – pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible

You can dump the whole PostgreSQL cluster with pg_dumpall. That's all the databases and all the globals for a single cluster. From the command line on the server, I'd do something like this. (Mine's listening on port 5433, not on the default port.) You may or may not need the --clean option.

$ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql

This includes the globals--information about users and groups, tablespaces, and so on.

If I were going to backup a single database and move it to a scratch server, I'd dump the database with pg_dump, and dump the globals with either

• pg_dumpall --globals-only, or
• pg_dumpall --roles-only (if you only need roles)

like this.

$ pg_dump -U postgres -h localhost -p 5433 --clean --file=sandbox.sql sandbox
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --globals-only --file=globals.sql

Outputs are just text files.

After you move these files to a different server, load the globals first, then the database dump.

$ psql -U postgres -h localhost -p 5433 < globals.sql
$ psql -U postgres -h localhost -p 5433 < sandbox.sql

I thought pg_dumpall would at least backup foreign keys, but even that seems to be an 'option'. According to: http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even with pg_dumpall I need to use a -o option to backup foreign keys

No, that reference says "Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used." (Emphasis added.) I think it's unlikely that your application references the OID columns. You don't need to use this option to "backup foreign keys". (Read the dump file in your editor or file viewer.)

What the error message suggests is a bit flipped in this pg_toast_16431418 relation on the exact place where the size is stored (the data size has the information that it's toasted in its upper bits). See TOAST in the 8.4 doc for details.

This does not suggest a problem with the page header, or with an index, or with the catalog.

BUG #5929 indicates it may be caused by a hardware problem, especially if the server doesn't use ECC memory.

You might want to try pageinspect to peek at the faulty data. If the damage is just a flipped bit, in theory it may be flipped back by writing directly at the data page as the last resort.

There are many steps involved:

1. do a file-level copy with the db shut down and work on the copy. Turn off autovacuum or anything that may touch the data.
2. find the main table corresponding to the pg_toast table. See Postgres pg_toast in autovacuum - which table?
3. Select each row of this table one by one until the failure occurs. Note the ctid of the row. It contains the offset of the page containing the row, and the offset of the row withing the page.
4. Dump the page with get_raw_page(relname text, blkno int) and examine the row. Use postgres doc to parse the data. You want to extract the pointer into the pg_toast_NNN relation.
5. Convert the pointer into a page (I don't know how to do that. I'd need to dive into the doc). Dump the corresponding page of pg_toast_NNN with get_raw_page() again.

It's no small feat if you don't have any prior knowledge of any of that stuff. All the information is in the documentation, but in a compact form.