How to solve the lack of distribution policy when using Pg_dump backup data structure in Greenplum three questions

Greenplum metadata errors can also affect the data backup process, and the workaround for this error is described in this article when a backup of a data structure using PG_DUMP results in a backup failure due to a lack of distribution policy.

Phenomenon

When you use the Pg_dump command to back up the data structure of the entire Greenplum database:

-f /data/dailybak/dw-nodata-$(date+%Y%m%d%H%M%-v-F-p5432  -h-C-d-s

After reporting the following error, the backup process terminates:

pg_dumpof"table1"data

Analysis

After this error occurs, the OID of this relation is found from the Pg_class metadata table:

psql databasename -c "select oid,* from pg_class where relname=‘table1‘;"

Assuming that the output OID is 1234,relnamespace to 5678, if there are more than one, it is processed by one, and a record of localoid for this OID is looked up from Gp_distribution_policy:

psql databasename -c "select oid,* from gp_distribution_policy where localoid=1234;"

This query should return 0 records from Pg_namespace to find the namespace name that this table belongs to:

psql databasename -c "select * from pg_namespace where oid=5678;"

Assuming that Nspname is ns1, this proves that Ns1.table1 has no distribution policy, so the backup failed

Solve

The basic idea is to set the distribution policy for Ns1.table1:

• Root Login Master Host
• Switch to the Greenplum administrator user, such as gpadmin:

su - gpadmin

• To connect to a database using Psql:

psql databasename

• Set up a random distribution policy

alter table ns1.table1 set distributed randomly;

or set the specified column as the distribution basis

alter table ns1.table1 set distributed by (column1);

If this modification is an error:

ERROR:  SETBYnoton non-distributed tables

Discard the next redistribution, drop the ns1.table1 and rebuild.

• Re-distribution

alter table ns1.table1 set with(reorganize=true);

• After rebuilding or redistribution, view the results of the changes:

databasename=# \d ns1.table1

The basic information of the table should be returned

select oid,* from pg_class where relname=‘table1‘;

If rebuilt, it is assumed that the OID returned is 12345, otherwise it is 1234.

select oid,* from gp_distribution_policy where localoid=12345;

The distribution policy for this table should be returned

• Pg_dump is a nasty guy, it stops as soon as it encounters an error, resolves it to continue down, if it encounters an error again, to prevent this type of error from continuing, use the following command to query a table in ns1 that does not have a distribution key value, which may contain views, Select the user tables that need to be processed and follow the steps above:

select relname from pg_class where relnamespace=5678 and oid not in (select localoid from gp_distribution_policy);

Other

Additional questions with two pg_dump:

• Hint Pg_class.oid is a field that does not exist, if encountered, to check the error message is just the namespace is not a user-defined table called Pg_class, if so, rename or drop it, why to build also system A table of the same name as the catalog ... Three days, a full three days ...

Pg_dump:sql Command Failedpg_dump:error message fromServer:ERROR:column C.oid does notExistline1:... spname=' DataService '  andC.relnamespace=n.oid andC.oid = p .... ^pg_dump:the command was:SELECTAttrnums fromPg_namespace asN, Pg_class asC, Gp_distribution_policy asPWHEREC.relname =' table1 '  andN.nspname=' ns1 '  andC.relnamespace=n.oid andC.oid = p.localoidpg_dump: * * * aborted because ofError

• When using the-o parameter for pg_dump, it is suggested that some tables do not exist, but these tables do not exist in the pg_class of any node and cannot be drop, by creating the Ya

pg_dumpSQL command failedpg_dumpError message from server: ERROR:  relation "ns1.table1" does not existpg_dumpThe command was: LOCK TABLE ns1.table1IN ACCESS SHARE MODEpg_dump*** aborted because of error

How to resolve the lack of distribution policy when using Pg_dump to back up data structures in Greenplum three questions