Hi ,
While testing something else ,i found 1 scenario where pg_dump is failing
Below is the standalone scenario -
--connect to psql terminal and create 2 database
postgres=# create database db1;
CREATE DATABASE
postgres=# create database db2;
CREATE DATABASE
--Connect to database db1 and run these below bunch of sql ( got from
vacuum.sql file)
\c db1
create temp table vaccluster (i INT PRIMARY KEY);
ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey;
CLUSTER vaccluster;
CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
AS 'ANALYZE pg_am';
CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
AS 'SELECT $1 FROM do_analyze()';
CREATE INDEX ON vaccluster(wrap_do_analyze(i));
INSERT INTO vaccluster VALUES (1), (2);
--Take the dump of db1 database ( ./pg_dump -Fp db1 > /tmp/dump.sql)
--Restore the dump file into db2 database
You are now connected to database "db2" as user "tushar".
db2=# \i /tmp/dump.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
psql:/tmp/dump.sql:71: ERROR: function do_analyze() does not exist
LINE 1: SELECT $1 FROM do_analyze()
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT $1 FROM do_analyze()
CONTEXT: SQL function "wrap_do_analyze" during inlining
db2=#
Workaround -
reset search_path ; before 'create index' statement in the dump.sql file .
--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company
