Re: moving a database to a new 15.1 server

Matthias Apitz schrieb am 01.02.2023 um 13:24:
> $ psql -Ulbs_lbsoclc01_dev_r1_dbo_u lbs_lbsoclc01_dev_r1
> psql (15.1)
> Type "help" for help.
>
> lbs_lbsoclc01_dev_r1=#
> lbs_lbsoclc01_dev_r1=# \d
> Did not find any relations.
> lbs_lbsoclc01_dev_r1=# \d dbo.accession_index
> Table "dbo.accession_index"
> Column | Type | Collation | Nullable | Default
> ------------------+-----------------------+-----------+----------+---------
> iln | smallint | | not null |
>
> lbs_lbsoclc01_dev_r1=# select count(*) from counter;
> ERROR: relation "counter" does not exist
> LINE 1: select count(*) from counter;
> ^
> lbs_lbsoclc01_dev_r1=# select count(*) from dbo.counter;
> count
> -------
> 41
>
> i.e. I have to specify the schema 'dbo' to access the tables.

you need to change the search_path

alter user lbs_lbsoclc01_dev_r1_dbo_u set search_path = dbo;

This was probably done on the old server. You can see the user's definition
if you dump the "globals" from the old server:

pg_dumpall -U postgres --globals-only -f globals.sql