Postgres restore missing privileges

Question

I am running through some very basic tests to confirm a backup/restore process is working on a local environment.

the issue I am running into is that it doesn't look as though pg_dump/pg_restore/psql is restoring a database to the same state.

A sample of what I am doing below from start to finish.

CREATE DATABASE testdb WITH ENCODING='UTF8' CONNECTION LIMIT=-1;

CREATE TABLE a
(
    a INT
);

INSERT INTO a(a)
SELECT 1 UNION ALL
SELECT 2;

SELECT * FROM a;

GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO testuser;

Then running the pg_dump

pg_dump -Fc -v --host=localhost --username=postgres --dbname=testdb -f C:\test\testdb.dump

creating a side by side restore for this example

CREATE DATABASE testdb_restore WITH ENCODING='UTF8' CONNECTION LIMIT=-1;

pg_restore -v --host=localhost --username=postgres --dbname=testdb_restore C:\test\testdb.dump

Now when I right click on testdb in pgadmin and click "Create Script" I get the following

-- Database: testdb

-- DROP DATABASE testdb;

CREATE DATABASE testdb
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_Australia.1252'
    LC_CTYPE = 'English_Australia.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

GRANT ALL ON DATABASE testdb TO postgres;

GRANT TEMPORARY, CONNECT ON DATABASE testdb TO PUBLIC;

GRANT ALL ON DATABASE testdb TO testuser;

When I click on the perform the same on testdb_restore, I get the following

-- Database: testdb_restore

-- DROP DATABASE testdb_restore;

CREATE DATABASE testdb_restore
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_Australia.1252'
    LC_CTYPE = 'English_Australia.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

As you can see I am missing the extra privileges from the original database.

I'm sure this is a very simple thing but I am currently lost on this one. I have also tried using methods and also pg_dump create database option added in and no difference.

Please note: I am extremely new to postgres and coming from a SQL Server background.

Answer 1

Roles and permissions are stored and managed per cluster, not per database, which is why pg_dump is not dumping them. You should use pg_dumpall if you are happy to have a dump of the whole cluster.

Alternatively, you can use pg_dumpall -r to dump roles only, and then pg_dump your database, and apply both scripts.