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

```none
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;

```

```none
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.