CODEWITHSUNDEEP
postgresqlhstorepg-restore
duduklein
duduklein

Reputation: 10624

Can not install hstore extension to new created schema

I have an ubuntu 18.04 with postgres 9.5 installed.

My db "mydb" has the hstore installed. When I do "\dx store", I do have 

List of installed extensions
  Name  | Version | Schema |                   Description                    
--------+---------+--------+--------------------------------------------------

hstore | 1.3     | public | data type for storing sets of (key, value) pairs
(1 row)

When I do a pg_restore with a certain backup file, a new schema also called "mydb" is created, but it does not contain the "hstore" extension. The result of the "\dx" command is the same. hstore is in my template1 already.

The pg_restore fails with

pg_restore: [archiver (db)] could not execute query: ERROR: type "hstore" does not exist

Can anyone point out where the problem is?

Thanks

Upvotes: 5

Views: 2090

Answers (1)

vinh
vinh

Reputation: 211

Was the backup created with the same version of postgresql? The dump / restore cycle generally assumes that the database was created in advance, but the dump should create the extensions. Can you search the backup to see if it contains a directive to create the extension for your schema:

     CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;

  • you will need to use the pg_restore command to stdout e.g. 

    pg_restore mydump.dump|more

You may also need to check that your schema is created before the extension, if you are not using the public schema.

Can you try the following to create the hstore extension in your mydb schema before the restore:

     CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA mydb;

Alternatively, the hstore extension may not be installed properly.

    # locate hstore|grep postgresql
    # dpkg -S /path/to/hstore.so

Upvotes: 3

Related Questions