4

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.

1
  • Completely unrelated, but INSERT INTO a(a) SELECT 1 UNION ALL SELECT 2; can be simplified to INSERT INTO a(a) values (1),(2); Commented Oct 16, 2019 at 7:10

3 Answers 3

5

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.

Sign up to request clarification or add additional context in comments.

Comments

3

Unfortunately the privileges for a database are not included in a pg_dump. You'd have to use pg_dumpall for that, but that dumps all databases.

I know this is annoying. It is a bug of long standing that nobody has fixed yet.

3 Comments

Not even with pg_restore --create ...?
I have not tested it recently, but I think not even with -C.
They are not included by default. Running pg_dump with --create flag should dump all the database-level permission such as those mentioned by OP
1

Quite a late answer, but hopefully it will help someone. Starting with PostgreSQL version 11, when you execute pg_dump with the --create flag, it also dumps the GRANT/REVOKE permissions of the database.

pg_dumpall --globals-only exports only roles and tablespace related attributes which are declared, as mentioned by @ADEpt, at cluster level.

See Make pg_dump dump the properties of a database, not just its contents (Haribabu Kommi)

Previously, attributes of the database itself, such as database-level GRANT/REVOKE permissions and ALTER DATABASE SET variable settings, were only dumped by pg_dumpall. Now pg_dump --create and pg_restore --create will restore these database properties in addition to the objects within the database. pg_dumpall -g now only dumps role- and tablespace-related attributes. pg_dumpall's complete output (without -g) is unchanged.

So, by adding the --create flag to pg_dump, the backup script will include all relevant GRANT ALL or specific grant SQL statements for the database.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.