Trap When Run Parallel Postgresql-11 And Prev

From iDempiere en
Jump to navigation Jump to search

Story

I install postgresql-10 on linux mint for long time.

i have export and import command to maintain database

 DATE_STR=$( date +%Y%m%d_%H%M%S )
 pg_dump --host localhost --port 5432 --username "adempiere" --no-password --format custom --blobs --compress 9 --file "idempiere-5.1-motive-test.${DATE_STR}.backup" "idempiere-5.1-motive-test"
 pg_restore --host localhost --port 5432 --username "adempiere" --dbname "idempiere-5.1-motive-test" --no-password "/home/hieplq/idempiere-5.1-motive.20181124_132436.backup"

I use it for long time without any issue so smooth

some week ago postgrest just release postgresql-11, so my mint ask for upgrate.

because it still keep postgres-10 i think no problem when have both, so do upgrade

everything still do well, postgres-10 still default database engine. so i just leave everything without any worry.

just some days ago (i think black friday is come soon to me) i export a database from my friend, it's a window server.

do pg_restore on my mint i get some warning but still get full idempiere database so just ignore warning

play some things with it, export and import to centos server i get error same same bellow error

Error message when import
psql -q -h localhost -p 5432 -U adempiere -d "idempiere-5.1-motive-test" -c 'CREATE EXTENSION "uuid-ossp"'

hieplq@hieplq-Inspiron-7460:~$ pg_restore --host localhost --port 5432 --username "adempiere" --dbname "idempiere-5.1-motive-test" --no-password "/home/hieplq/idempiere-5.1-motive.20181124_132436.backup" pg_restore: WARNING: operator attribute "function" not recognized pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 6197; 2617 89294 OPERATOR + adempiere pg_restore: [archiver (db)] could not execute query: ERROR: operator procedure must be specified

   Command was: CREATE OPERATOR adempiere.+ (
   FUNCTION = adempiere.adddays,
   LEFTARG = timestamp with time zone,
   RIGHTARG = numeric,
   COMMUTATOR = OPERATOR(adempiere.+)

);


pg_restore: [archiver (db)] could not execute query: ERROR: operator does not exist: timestamp with time zone adempiere.+ numeric

   Command was: ALTER OPERATOR adempiere.+ (timestamp with time zone, numeric) OWNER TO adempiere;


pg_restore: WARNING: operator attribute "function" not recognized pg_restore: [archiver (db)] Error from TOC entry 6199; 2617 89296 OPERATOR + adempiere pg_restore: [archiver (db)] could not execute query: ERROR: operator procedure must be specified

   Command was: CREATE OPERATOR adempiere.+ (
   FUNCTION = adempiere.adddays,
   LEFTARG = interval,
   RIGHTARG = numeric,
   COMMUTATOR = OPERATOR(adempiere.-)

);


pg_restore: [archiver (db)] could not execute query: ERROR: operator does not exist: interval adempiere.+ numeric

   Command was: ALTER OPERATOR adempiere.+ (interval, numeric) OWNER TO adempiere;


pg_restore: WARNING: operator attribute "function" not recognized pg_restore: [archiver (db)] Error from TOC entry 6201; 2617 89298 OPERATOR - adempiere pg_restore: [archiver (db)] could not execute query: ERROR: operator procedure must be specified

   Command was: CREATE OPERATOR adempiere.- (
   FUNCTION = adempiere.subtractdays,
   LEFTARG = timestamp with time zone,
   RIGHTARG = numeric,
   COMMUTATOR = OPERATOR(adempiere.-)

);

first thing come in my mind, window is still worse.

try to export my old db and import to centos still get same issue

ok maybe from postgres 10.6 something change make mint and centos is incompatible

try export and import on my mint still get same issue

recall memory about Migration to Version 11 so maybe postgres-11 effect.

i check version of "pg_dump --version" "pg_restore --version" it show 10.6 isn't 11

go to psql to check database engine

 sudo su - postgres
 psql
 it show "psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1), server 10.6 (Ubuntu 10.6-1.pgdg18.04+1))"
 

database engine look ok it's 10.6

just try to remove postgresql-11-server and restart mint

re-test but issue will there

no more idea. just try to check engine again

 sudo su - postgres
 psql
 it show "psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1), server 10.6 (Ubuntu 10.6-1.pgdg18.04+1))"

huh. what's "psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1)" i already removed all postgresql-11*

google said it's postgres client and package name postgresql-client-11

try to remove it also

 sudo apt-get remove postgresql-client-11

do test again, success with every case so postgres come back to me

one more time why naming convention is important and my logic isn't other one logic

the question now in case i want to use both version of postgres-client, how to choose client when use exp/imp other other command for each postgres instance