r/PostgreSQL•
eldenv

beginner question - restoring from a dump

Hello, trying to restore a database from this link: https://data.london.gov.uk/download/london-development-database-sql-extract/e167dbc8-3a65-4e4b-b5e8-7aaac7278399/ldddata_180523.sql.tar

This file provides some instructions at the bottom, which say:

Create the ldd user

     create role ldd login  nosuperuser inherit nocreatedb nocreaterole noreplication; 

Create the app_ldd schema

    create schema app_ldd  authorization ldd;

Import the database:
as the postgres user:

    pg_restore --clean -d  <LDD_DATABASE> ldddata_20170609.sql.tar 
Note: The database needs to be a postgis 2.0+ enabled

I'm on Windows 10, PostgresQL 11.

I've tried following these instruction through the psql command line, and nothing happens.

I have also tried restoring through the pgAdmin 4 GUI in a browser, with different settings, and each time it throws a load of errors and fails different ways.

e.g.:

pg_restore: [archiver (db)] Error from TOC entry 4147; 0 0 ACL TABLE plan_table ldd
pg_restore: [archiver (db)] could not execute query: ERROR:  role "app_ldd_readonly" does not exist

Any pointers as to what's going wrong?

Posted Nov 8, 2018, 8:57 PM UTC

21
Sedifutka•

Looks like you must create an additional role named app_ldd_readonly

1