Question

Issue with aiven_extras extension when restoring postres dump outside of DigitalOcean

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.


Submit an answer


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!

Sign In or Sign Up to Answer

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.

Bobby Iliev
Site Moderator
Site Moderator badge
July 30, 2024
Accepted Answer

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:

  1. Download the Extension from the repository that you’ve shared: https://github.com/aiven/aiven-extras

  2. Once you have the extension files, copy them to your local PostgreSQL extension directory. The directory is usually /usr/share/postgresql/<version>/extension/.

    sudo cp aiven_extras.control /usr/share/postgresql/16/extension/
    sudo cp aiven_extras--*.sql /usr/share/postgresql/16/extension/
    
  3. Connect to your local PostgreSQL instance and create the extension.

    CREATE EXTENSION aiven_extras;
    

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:

  1. Modify your pg_dump command to exclude the aiven_extras extension:

    pg_dump -Fc -d $db -h $DB_HOST -p $DB_PORT -U $DB_USER --exclude-schema=aiven_extras -f $BACKUP_DIR/$db/$db.dump
    
  2. If you’ve already created the dump, you can remove references to aiven_extras before restoring:

    pg_restore -l your_dump_file.dump | grep -v aiven_extras > dump.list
    pg_restore -L dump.list your_dump_file.dump
    

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

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Become a contributor for community

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

DigitalOcean Documentation

Full documentation for every DigitalOcean product.

Resources for startups and SMBs

The Wave has everything you need to know about building a business, from raising funding to marketing your product.

Get our newsletter

Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.

New accounts only. By submitting your email you agree to our Privacy Policy

The developer cloud

Scale up as you grow — whether you're running one virtual machine or ten thousand.

Get started for free

Sign up and get $200 in credit for your first 60 days with DigitalOcean.*

*This promotional offer applies to new accounts only.