Comment résoudre les problèmes de privilèges lors de la restauration d'une base de données PostgreSQL ?

Question

J'ai fait une sauvegarde propre, sans propriétaire, de la base de données Postgres avec la commande

pg_dump sample_database -O -c -U

Plus tard, lorsque je restaure la base de données avec

psql -d sample_database -U app_name

Cependant, j'ai rencontré plusieurs erreurs qui m'empêchent de restaurer les données :

ERROR:  must be owner of extension plpgsql
ERROR:  must be owner of schema public
ERROR:  schema "public" already exists
ERROR:  must be owner of schema public
CREATE EXTENSION
ERROR:  must be owner of extension plpgsql

J'ai creusé dans le texte clair de SQL pg_dump génère et j'ai trouvé qu'il contient du SQL

CREATE SCHEMA public;
COMMENT ON SCHEMA public IS 'standard public schema';
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

Je pense que les causes sont que l'utilisateur app_name n'a pas les privilèges nécessaires pour modifier l'adresse de l'utilisateur. public et plpgsql .

Comment pourrais-je résoudre ce problème ?

Answer 1

Certaines des réponses ont déjà fourni diverses approches liées à la suppression de l'extension de création et du commentaire sur les extensions. Pour moi, la ligne de commande suivante a semblé fonctionner et être l'approche la plus simple pour résoudre le problème :

cat /tmp/backup.sql.gz | gunzip - | \
  grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' |  \
    psql --set ON_ERROR_STOP=on -U db_user -h localhost my_db

Quelques notes

• La première ligne ne fait que décompresser ma sauvegarde et vous devrez peut-être l'ajuster en conséquence.
• La deuxième ligne utilise grep pour se débarrasser des lignes incriminées.
• la troisième ligne est ma commande psql ; il se peut que vous deviez l'adapter car vous utilisez normalement psql pour la restauration.