Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNCAK-0001SQ-27 for pgsql-general@arkaria.postgresql.org; Wed, 01 Feb 2023 12:24:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pNCAI-000072-Tl for pgsql-general@arkaria.postgresql.org; Wed, 01 Feb 2023 12:24:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNCAI-000063-J6 for pgsql-general@lists.postgresql.org; Wed, 01 Feb 2023 12:24:38 +0000 Received: from ms-10.1blu.de ([178.254.4.101]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNCAG-0003eo-3s for pgsql-general@postgresql.org; Wed, 01 Feb 2023 12:24:38 +0000 Received: from [178.254.11.41] (helo=sh4-5.1blu.de) by ms-10.1blu.de with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1pNCAF-0005wa-7J for pgsql-general@postgresql.org; Wed, 01 Feb 2023 13:24:35 +0100 Date: Wed, 1 Feb 2023 13:24:34 +0100 From: Matthias Apitz To: Postgres General Subject: moving a database to a new 15.1 server Message-ID: <20230201122434.GB9@sh4-5.1blu.de> Reply-To: Matthias Apitz MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit X-Operating-System: FreeBSD 12.0-CURRENT r314251 (amd64) X-message-flag: Mails containing HTML will not be read! Please send only plain text. User-Agent: Mutt/1.9.4 (2018-02-28) X-Con-Id: 51246 X-Con-U: 0-guru X-Originating-IP: 178.254.11.41 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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