Skip to main content

How to check your Postgres binaries are suitable for your migration

shanshan zhao
  • Updated

Background

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

It highlights the version of Postgres binaries must be same as target database here. 

"They must be the same version as the Postgres version of the target database. For example, if you want to import a PostgreSQL 10 database from your private network into a PostgreSQL 14 database in BigAnimal, use the client libraries and binaries from version 14."

However, Postgres community client package is incompatible with the migration between EPAS clusters. If you are using Postgres Community client package to migrate EPAS , 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, resolve this incompatibility issue.

How to check

Check the version and type of the binaries.

psql --version

pg_restore --version

pg_dump --version

pg_dumpall --version

Example 1

By checking the output as below, its version is 14.4 and it's compatible with Postgres Community. So you could migrate from Postgres to BigAnimal Postgres v14

❯ pg_dumpall --version
pg_dumpall (PostgreSQL) 14.4

Example 2

By checking the output as below, its version is 11 and it's compatible with EPAS. So you could migrate from EPAS to BigAnimal Oracle compatible v11 

# pg_dump --version
pg_dump (EnterpriseDB) 11.16.26

Please note, all the binaries should be same version and has 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