Thread

1. moving a database to a new 15.1 server

   Matthias Apitz <guru@unixarea.de> — 2023-02-01T12:24:34Z

   Hello,
   
   The source database in the 12.11 server shows this:
   
   $ psql -Ulbs_lbsoclc01_dev_r1_dbo_u $DATABASE
   psql (12.11)
   Type "help" for help.
   
   lbs_lbsoclc01_dev_r1=>
   lbs_lbsoclc01_dev_r1=> \d
                                 List of relations
    Schema |             Name              | Type  |           Owner            
   --------+-------------------------------+-------+----------------------------
    dbo    | accession_index               | table | lbs_lbsoclc01_dev_r1_dbo_u
    dbo    | acq_bind                      | table | lbs_lbsoclc01_dev_r1_dbo_u
    ... 
   
   I dumped this with pg_dump 
   
   $ pg_dump --file=dmp.gz --compress=9 --dbname=$DATABASE
   
   Created in the new server the database
   
   $ createdb -U lbs_lbsoclc01_dev_r1_dbo_u -T template0 lbs_lbsoclc01_dev_r1
   
   and the role for thw user with
   
   CREATE ROLE lbs_lbsoclc01_dev_r1_dbo_u WITH SUPERUSER CREATEDB LOGIN PASSWORD 'xxxxxx' ;
   CREATE ROLE
   
   and loaded the dump with
   
   $ gzip -dc dmp.gz | psql -U lbs_lbsoclc01_dev_r1_dbo_u lbs_lbsoclc01_dev_r1
   
   This all went fine. But when I now look into the database:
   
   $ 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.
   
   What I am missing here in this move?
   
   	matthias
   
   
   
   -- 
   Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
   Public GnuPG key: http://www.unixarea.de/key.pub
   
   
   
   

2. Re: moving a database to a new 15.1 server

   shammat@gmx.net — 2023-02-01T12:35:00Z

   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
   
   
   
   
   
   
   
   

3. Re: moving a database to a new 15.1 server

   Erik Wienhold <ewie@ewie.name> — 2023-02-01T12:38:11Z

   > On 01/02/2023 13:24 CET Matthias Apitz <guru@unixarea.de> wrote:
   >
   > The source database in the 12.11 server shows this:
   >
   > $ psql -Ulbs_lbsoclc01_dev_r1_dbo_u $DATABASE
   > psql (12.11)
   > Type "help" for help.
   >
   > lbs_lbsoclc01_dev_r1=>
   > lbs_lbsoclc01_dev_r1=> \d
   >                               List of relations
   >  Schema |             Name              | Type  |           Owner
   > --------+-------------------------------+-------+----------------------------
   >  dbo    | accession_index               | table | lbs_lbsoclc01_dev_r1_dbo_u
   >  dbo    | acq_bind                      | table | lbs_lbsoclc01_dev_r1_dbo_u
   >  ...
   >
   > I dumped this with pg_dump
   >
   > $ pg_dump --file=dmp.gz --compress=9 --dbname=$DATABASE
   >
   > Created in the new server the database
   >
   > $ createdb -U lbs_lbsoclc01_dev_r1_dbo_u -T template0 lbs_lbsoclc01_dev_r1
   >
   > and the role for thw user with
   >
   > CREATE ROLE lbs_lbsoclc01_dev_r1_dbo_u WITH SUPERUSER CREATEDB LOGIN PASSWORD 'xxxxxx' ;
   > CREATE ROLE
   >
   > and loaded the dump with
   >
   > $ gzip -dc dmp.gz | psql -U lbs_lbsoclc01_dev_r1_dbo_u lbs_lbsoclc01_dev_r1
   >
   > This all went fine. But when I now look into the database:
   >
   > $ 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.
   >
   > What I am missing here in this move?
   
   Your search_path does not contain dbo.  Check  SHOW search_path;  in the old
   database and set the search_path with ALTER DATABASE in the new database accordingly.
   
   --
   Erik