CODEWITHSUNDEEP
postgresqlpg-restore
JAN
JAN

Reputation: 21875

PG_Restore fails when restoring Database

When running restore from Production to my Local Machine using :

pg_restore server_employees.tar -d server_employees

I get this:

tore: from TOC entry 2; 3079 16570 EXTENSION set_user (no owner)
pg_restore: error: could not execute query: ERROR:  could not open extension control file "/usr/share/postgresql/14/extension/set_user.control": No such file or directory
Command was: CREATE EXTENSION IF NOT EXISTS set_user WITH SCHEMA public;


pg_restore: from TOC entry 3260; 0 0 COMMENT EXTENSION set_user 
pg_restore: error: could not execute query: ERROR:  extension "set_user" does not exist
Command was: COMMENT ON EXTENSION set_user IS 'similar to SET ROLE but with added logging';


pg_restore: from TOC entry 301; 1259 16592 VIEW pg_stat_statements postgres
pg_restore: error: could not execute query: ERROR:  column reference "wal_bytes" is ambiguous
LINE 33:     pg_stat_statements.wal_bytes
             ^
Command was: CREATE VIEW metric_helpers.pg_stat_statements AS
 SELECT pg_stat_statements.userid,
    pg_stat_statements.dbid,
    pg_stat_statements.queryid,
    pg_stat_statements.query,
    pg_stat_statements.plans,
    pg_stat_statements.total_plan_time,
    pg_stat_statements.min_plan_time,
    pg_stat_statements.max_plan_time,
    pg_stat_statements.mean_plan_time,
    pg_stat_statements.stddev_plan_time,
    pg_stat_statements.calls,
    pg_stat_statements.total_exec_time,
    pg_stat_statements.min_exec_time,
    pg_stat_statements.max_exec_time,
    pg_stat_statements.mean_exec_time,
    pg_stat_statements.stddev_exec_time,
    pg_stat_statements.rows,
    pg_stat_statements.shared_blks_hit,
    pg_stat_statements.shared_blks_read,
    pg_stat_statements.shared_blks_dirtied,
    pg_stat_statements.shared_blks_written,
    pg_stat_statements.local_blks_hit,
    pg_stat_statements.local_blks_read,
    pg_stat_statements.local_blks_dirtied,
    pg_stat_statements.local_blks_written,
    pg_stat_statements.temp_blks_read,
    pg_stat_statements.temp_blks_written,
    pg_stat_statements.blk_read_time,
    pg_stat_statements.blk_write_time,
    pg_stat_statements.wal_records,
    pg_stat_statements.wal_fpi,
    pg_stat_statements.wal_bytes
   FROM metric_helpers.pg_stat_statements(true) pg_stat_statements(userid, dbid, queryid, query, plans, total_plan_time, min_plan_time, max_plan_time, mean_plan_time, stddev_plan_time, calls, total_exec_time, min_exec_time, max_exec_time, mean_exec_time, stddev_exec_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time, wal_records, wal_fpi, wal_bytes);


pg_restore: error: could not execute query: ERROR:  relation "metric_helpers.pg_stat_statements" does not exist
Command was: ALTER TABLE metric_helpers.pg_stat_statements OWNER TO postgres;

Any idea how to fix this ?

Upvotes: 0

Views: 1141

Answers (1)

jian
jian

Reputation: 4867

--install extension
source: https://github.com/pgaudit/set_user

gh repo clone pgaudit/set_user
cd set_user
make PG_CONFIG=/usr/local/pgsql15/bin/pg_config
sudo make install PG_CONFIG=/usr/local/pgsql15/bin/pg_config

In here

make PG_CONFIG=/usr/local/pgsql15/bin/pg_config

replace "/usr/local/pgsql15/bin/pg_config" to the binary app pg_config path in your computer.

then create extension in your new database cluster via psql or other client.

CREATE EXTENSION set_user;

In here, your new database cluster at least need at two extensions: set_user,pg_stat_statements. So make these these two available in the cluster.

pg_dump example:

/usr/local/pgsql15/bin/pg_dump -d test15 --format=tar --extension=set_user --extension=pg_stat_statements  --table=test >test.tar

if you not use --extension, then all the old cluster extensions should be in the new cluster.

Restore to a new database:

/usr/local/pgsql15/bin/createdb -T template0 newdb1

/usr/local/pgsql15/bin/pg_restore --verbose -d newdb1 test.tar

In: https://www.postgresql.org/docs/current/app-pgdump.html search keyword: --extension=pattern

Upvotes: 1

Related Questions