I have a script that backs up our managed PostgreSQL database cluster from DigitalOcean (Attached below). The backup works fine, but I’m having some issues when I try to restore the databases locally.
When I do that I get the following error:
pg_restore: from TOC entry 4819; 0 0 COMMENT EXTENSION aiven_extras
pg_restore: error: could not execute query: ERROR: extension "aiven_extras" does not exist
Command was: COMMENT ON EXTENSION aiven_extras IS 'aiven_extras';
I have found the aiven-extras repo, where the only instructions for installing the extension is to run CREATE EXTENSION aiven_extras;
. But I suspect that there is something else I need to do. There seems to be very little information about this, however. If I run the command anyway I get:
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3; 3079 18759 EXTENSION aiven_extras (no owner)
pg_restore: error: could not execute query: ERROR: could not open extension control file "/usr/share/postgresql/14/extension/aiven_extras.control": No such file or directory
Command was: CREATE EXTENSION IF NOT EXISTS aiven_extras WITH SCHEMA aiven_extras;
The (important part of) the backup script looks like this:
# Back up users, roles, etc.
pg_dumpall -h $DB_HOST -p $DB_PORT -U $DB_USER --globals-only --no-role-passwords -f $BACKUP_DIR/globals/globals.sql
# List all databases.
databases=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d')
# Remove databases that should not be backed up.
databases=$(echo "$databases" | grep -v -E "template0|template1|postgres|_dodb|defaultdb")
# Back up each database.
for db in $databases; do
mkdir -p $BACKUP_DIR/$db
pg_dump -Fc -d $db -h $DB_HOST -p $DB_PORT -U $DB_USER -f $BACKUP_DIR/$db/$db.dump
done
And the restore (to a new, local, empty database cluster):
psql -h localhost -U postgres -p 5432 < globals.sql
for db in $databases; do
pg_restore -d postgres -h localhost -U postgres -p 5432 -C $db.dump
done
Do I need to install the aiven_extras extension before doing a restore, or can/should I modify something in the backup script instead?
Thank you.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Hey!
As the extension is not part of the default Postgres extensions, you would need to download it first and add it to your Postgres directory in order to be able to install it with the
CREATE EXTENSION aiven_extras;
statement.To do that you would need to:
Download the Extension from the repository that you’ve shared: https://github.com/aiven/aiven-extras
Once you have the extension files, copy them to your local PostgreSQL extension directory. The directory is usually
/usr/share/postgresql/<version>/extension/
.Connect to your local PostgreSQL instance and create the extension.
Now that the
aiven_extras
extension is available locally, the restore process should be completed without errors.Another option here is to actually exclude the extension during the backup or remove references to it from the dump file before restoring. Here are a few ways to do that:
Modify your
pg_dump
command to exclude theaiven_extras
extension:If you’ve already created the dump, you can remove references to
aiven_extras
before restoring:The
aiven_extras
extension provides specific features for Aiven-managed databases. If you’re not using these features in your application, excluding it shouldn’t cause any issues.I hope this helps you get your restore working smoothly! Let me know if you have any more questions. Happy coding! 🚀
- Bobby