Re: moving a database to a new 15.1 server - Mailing list pgsql-general

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