Skip to main content

How to choose your Postgres binaries for your migration

Shanshan Zhao
  • Updated

Background

When migrating from Postgres to BigAnimal Clusters, you may follow the doc Importing an existing Postgres database.

Postgres community client package is incompatible with EPAS. And you may get the error like: 

❯ pg_dump -Fc -d "host=xx.xxx.xx.xx user=shan dbname=testdb port=5444 password=password" -f app.dump
pg_dump: WARNING: typtype of data type "aq$_recipient_list_t" appears to be invalid
pg_dump: WARNING: typtype of data type "chararr" appears to be invalid
pg_dump: WARNING: typtype of data type "desc_tab" appears to be invalid
pg_dump: WARNING: typtype of data type "dblink_array" appears to be invalid
pg_dump: WARNING: typtype of data type "index_table_type" appears to be invalid
pg_dump: WARNING: typtype of data type "instance_table" appears to be invalid
pg_dump: WARNING: typtype of data type "lname_array" appears to be invalid
pg_dump: WARNING: typtype of data type "name_array" appears to be invalid
pg_dump: WARNING: typtype of data type "number_array" appears to be invalid
pg_dump: WARNING: typtype of data type "uncl_array" appears to be invalid
pg_dump: WARNING: typtype of data type "aq$_reg_info_list" appears to be invalid
pg_dump: WARNING: typtype of data type "html_pieces" appears to be invalid
pg_dump: WARNING: typtype of data type "replies" appears to be invalid
//////////////////////////////////////////////////////////////////
pg_restore: [archiver (db)] Error from TOC entry 385; 1259 15605 TABLE edb$snap enterprisedb
pg_restore: [archiver (db)] could not execute query: ERROR: relation "edb$snap" already exists
Command was: CREATE TABLE sys."edb$snap" (
edb_id bigint NOT NULL,
dbname name,
snap_tm timestamp without time zone,
start_tm timestamp without time zone,
backend_id bigint,
comment text,
baseline_ind character(1)
);

This doc is going to introduce how to check, and resolve this incompatibility issue.

How to check

Step 1 Check the version and type of the binaries.

psql --version

pg_restore --version

pg_dump --version

pg_dumpall --version

Example 1

Postgres Community binaries as below

❯ pg_dumpall --version
pg_dumpall (PostgreSQL) 14.4

Example 2

EPAS binaries as below

# pg_dump --version
pg_dump (EnterpriseDB) 11.16.26

Please note, that all the binaries should be the same version and have the same compatibility.

How to downgrade the correct package

  1. Go to https://www.enterprisedb.com/software-downloads-postgres and login with your EDB account.
  2. Based on your migration scenario, 'Access Repo' or 'Download'
    Postgres Compatible - Download
    Oracle Compatible - Access Repo
    mceclip0.png
  3. [Access Repo], Now you will get your repo Username and repo password. Go to repo and get the installation script. mceclip1.png