Trap When Run Parallel Postgresql-11 And Prev
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;
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;
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
