Restoring a database restores to unexpected tablespace
Hi, Can someone point me in the right direction for this issue we are having -- our goal is to dump a database that is currently on a tablespace named data2 that we want to restore on the same server but on tablespace pg_default -- we tried other ways like: ALTER DATABASE "[database_name]" SET default_tablespace = [new_tablespace]; alter table all in tablespace data2 set tablespace pg_default; But we want to try it with a pgdump/psql. To reproduce on our end (Server 9.5): 1. create new database for the restore with the tablespace as pg_default 2. Dump the source database (currently on data2 tablespace) with the following command: sudo -u postgres pg_dump mydatabase --no-owner --no-tablespaces | gzip > mydatabase.gz 3. Restore the database with this command: zcat /var/backup/db/mydatabase.gz | sudo -H -u postgres psql --quiet -e -c 'SET default_tablespace = pg_default;' -f - mydatabase_test > /tmp/mydatabase_test.log What happens during the restore is that all tables are created on data2, not pg_default. Any help would be greatly appreciated. Thanks, Alex Sent with [ProtonMail](https://protonmail.com) Secure Email.
Re: Restoring a database restores to unexpected tablespace
Thanks Tom and Ian, Tom, I wasn't sure if that would work (-c), so I just tried assuming it would throw an error, but it didn't so I assumed it worked until I started checking the tables and noticed it was still being created on data2. I tried originally with just -f, but that didn't work so I added in -c and that didn't work either. Last night, I had an idea and it's working the way I expect it to now, this is what I did: 1. Dump Database, this time, no compression so I can search/grep it to see if it has any tablespace references, PLUS add in my own tablspace reference (See Step 2 for that): sudo -u postgres pg_dump --no-owner --no-tablespaces mydatabase > /var/lib/pgsql/dumps/mydatabase.dump 2. Set the default table to pg_default on the first line with sed: sed -i '1i SET default_tablespace = pg_default;' /var/lib/pgsql/dumps/mydatabase.dump 3. Restore the database: cat /var/lib/pgsql/dumps/mydatabase.dump | sudo -H -u postgres psql --quiet -e -c 'SET default_tablespace = pg_default;' -f - mydatabase_test > /tmp/mydatabase.log It's currently still restoring, will take about 2 more hours to complete, but so far so good. Also, Ian, good idea on that, but there was no issue of the db dump yesterday with path and possibly one path had a dump with tablespaces and the other didn't and that I possibly used the one with the tablespace in it...as I was in the directory for the dump and just explicitly wrote out the directory for the restore, and it's the first time I did this and the dump I took explicitly passed in no tablespaces. I couldn't read the file to be sure, so I added -e on the restore and checked the logs to see what it was doing and didn't see any mention of tablespaces. On the newer dump in plaintext, it also doesn't have any tablespace reference other than the one I added as the first line stating: SET default_tablespace = pg_default; Again, the restore to database was set with pg_default as the tablespace to use and the dump explicitly passed in the parameter for no tablespaces (and it looks like it didn't add in tablespace info) but on restore, it restored to tablespace data2 instead of the pg_default tablespace of the databaseonly after modifying the dump file by adding to the top line this: "SET default_tablespace = pg_default;" did it restore to the pg_default tablespace. Thanks again for your help! Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, July 10, 2019 10:20 AM, Tom Lane wrote: > Ian Barwick ian.barw...@2ndquadrant.com writes: > > > On 7/10/19 2:56 AM, Alex Williams wrote: > > > > > 3. Restore the database with this command: > > > zcat /var/backup/db/mydatabase.gz | sudo -H -u postgres > > > psql --quiet -e -c 'SET default_tablespace = pg_default;' -f - > > > mydatabase_test > /tmp/mydatabase_test.log > > > > > > This should work. > > Yeah, on modern PG. But I think psql didn't support combinations of > -c and -f switches until 9.6. 9.5 would simply have dropped that -c > switch. > > regards, tom lane
Tablespace column value null on select * from pg_tables
Hi, Server Version 9.5 I found this old thread on something similar to the results I'm getting: https://www.postgresql.org/message-id/1308615192339-4508750.post%40n5.nabble.com But in my case, I have a database that's in a user-defined tablespace (data2) and all the tables/indexes there are also in data2 and I want to do a select into a table the results of all the tables / tablespaces they are in that database...when doing this: SELECT distinct tablespace FROM pg_tables; I get 2 rows: null and pg_global (I think to expect null for pg_default, but if the table is in a user-defined tablespace, should we expect it to show it, in my case, data2?) or SELECT distinct tablespace FROM pg_indexes I get 3 rows: null, pg_global and pg_default and this: SELECT * FROM pg_tablespace; I get 3 rows: pg_default, pg_global and data2. Using pgadmin, getting properties for the DB / tables, it shows data2. What I want to do is move all the tables / indexes from data2 to pg_default (we added more space to the pg_default mount.) Now, I did a pg_dump/restore for one database which took a long time and we now know the process for that, so on the next database we have we want to do it where we use the following commands: ALTER DATABASE mydatabase SET TABLESPACE pg_default; alter table all in tablespace data2 set tablespace pg_default; But, what I'm trying to accomplish here is, aside from checking the filesystem, like df- h, to see it was moved or properties on each table (too many) I just want to run a query that will insert into a table all the tables and their tablespace names and when the above two commands (3rd will be moving indexes) run the query again and verify everything has moved from data2 to pg_default. Thanks for your help in advance. Alex Sent with [ProtonMail](https://protonmail.com) Secure Email.
Re: Tablespace column value null on select * from pg_tables
Hi Adrian, "Not if you did: CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ] ALTER DATABASE name SET TABLESPACE new_tablespace This makes the tablespace the default for the database and the default shows up as null in pg_tables: https://www.postgresql.org/docs/9.5/view-pg-tables.html tablespace name pg_tablespace.spcname Name of tablespace containing table (null if default for database)" Thanks, but I didn't do that. I have an existing database that's on data2 and haven't ran any command yet to change the db tablespace. When the db was created two years ago, it went directly to data2 along with any table/indexes to data2. The second command is the command I want to run but haven't ran it yet since I want to get the tablespaces for the tables on the db inserted into a table prior, so I can make sure all the tables in data2 go into pg_default by running the query again and seeing what tablespace they are in (at this point, it should probably be null for the tablespace name signifying it's pg_default.) PgAdmin seems has the proper query to get the db and table tablespace names (right click table/select properties), but the queries I've used from various sources like stackoverflow don't provide the correct named tablespace. Thanks, Alex Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Monday, July 15, 2019 3:22 PM, Adrian Klaver wrote: > On 7/15/19 11:35 AM, Alex Williams wrote: > > > Hi, > > Server Version 9.5 > > I found this old thread on something similar to the results I'm getting: > > https://www.postgresql.org/message-id/1308615192339-4508750.post%40n5.nabble.com > > But in my case, I have a database that's in a user-defined tablespace > > (data2) and all the tables/indexes there are also in data2 and I want to > > do a select into a table the results of all the tables / tablespaces > > they are in that database...when doing this: > > SELECT distinct tablespace > > FROM pg_tables; > > I get 2 rows: null and pg_global (I think to expect null for pg_default, > > but if the table is in a user-defined tablespace, should we expect it to > > show it, in my case, data2?) > > Not if you did: > > CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ] > > ALTER DATABASE name SET TABLESPACE new_tablespace > > This makes the tablespace the default for the database and the default > shows up as null in pg_tables: > > https://www.postgresql.org/docs/9.5/view-pg-tables.html > > tablespace name pg_tablespace.spcname Name of tablespace containing > table (null if default for database) > > > or > > SELECT distinct tablespace > > FROM pg_indexes > > I get 3 rows: null, pg_global and pg_default > > and this: SELECT * FROM pg_tablespace; > > I get 3 rows: pg_default, pg_global and data2. > > Using pgadmin, getting properties for the DB / tables, it shows data2. > > What I want to do is move all the tables / indexes from data2 to > > pg_default (we added more space to the pg_default mount.) > > Now, I did a pg_dump/restore for one database which took a long time and > > we now know the process for that, so on the next database we have we > > want to do it where we use the following commands: > > ALTER DATABASE mydatabase SET TABLESPACE pg_default; > > > alter table all in tablespace data2 set tablespace pg_default; > > But, what I'm trying to accomplish here is, aside from checking the > > filesystem, like df- h, to see it was moved or properties on each table > > (too many) I just want to run a query that will insert into a table all > > the tables and their tablespace names and when the above two commands > > (3rd will be moving indexes) run the query again and verify everything > > has moved from data2 to pg_default. > > Thanks for your help in advance. > > Alex > > Sent with ProtonMail https://protonmail.com Secure Email. > > -- > > Adrian Klaver > adrian.kla...@aklaver.com
Re: Tablespace column value null on select * from pg_tables
Hi Thomas and Adrian, I'm sorry on my part, you both are correct, thanks again for your help. What I did today that worked to move everything from data2 to pg_default was: 1. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default; ERROR: some relations of database "mydatabase" are already in tablespace "pg_default" HINT: You must move them back to the database's default tablespace before using this command. 2. Ran this to get the objects not in data2: SELECT t.relname, t.reltablespace, sp.spcname FROM pg_class t LEFT JOIN pg_tablespace sp ON sp.oid = t.reltablespace where spcname is not null 3. Ran this on those objects not on data2 (the current default TS) alter index public.my_index set tablespace data2; 4. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default; Viola, no issues, and the tables and indexes are now on pg_default tablespace. Thanks again to both of you! Alex (Just a note: The name of the actual DB / objects manually moved were renamed for this public post) Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Monday, July 15, 2019 8:33 PM, Adrian Klaver wrote: > On 7/15/19 12:53 PM, Alex Williams wrote: > > > Hi Adrian, > > "Not if you did: CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name > > ] ALTER DATABASE name SET TABLESPACE new_tablespace This makes the > > tablespace the default for the database and the default shows up as null in > > pg_tables: https://www.postgresql.org/docs/9.5/view-pg-tables.html > > tablespace name pg_tablespace.spcname Name of tablespace containing table > > (null if default for database)" > > Thanks, but I didn't do that. I have an existing database that's on data2 > > and haven't ran any command yet to change the db tablespace. When the db > > was created two years ago, it > > So someone else ran the command the end result is the same, data2 is the > default tablespace for the db so you get NULL in the tablespace column > in pg_tables. > > went directly to data2 along with any table/indexes to data2. The second > command is the command I want to run but haven't ran it yet since I want > to get the tablespaces for the tables on the db inserted into a table > prior, so I can make sure all the tables in data2 go into pg_default by > running the query again and seeing what tablespace they are in (at this > point, it should probably be null for the tablespace name signifying > it's pg_default.) > > That is where you are getting confused, there are two defaults in play; > pg_default and the db default. > > pg_default: > > https://www.postgresql.org/docs/9.5/manage-ag-tablespaces.html > "Two tablespaces are automatically created when the database cluster is > initialized. The pg_global tablespace is used for shared system > catalogs. The pg_default tablespace is the default tablespace of the > template1 and template0 databases (and, therefore, will be the default > tablespace for other databases as well, unless overridden by a > TABLESPACE clause in CREATE DATABASE)." > > db default: > > From same link above. > > "The tablespace associated with a database is used to store the system > catalogs of that database. Furthermore, it is the default tablespace > used for tables, indexes, and temporary files created within the > database, if no TABLESPACE clause is given and no other selection is > specified by default_tablespace or temp_tablespaces (as appropriate). If > a database is created without specifying a tablespace for it, it uses > the same tablespace as the template database it is copied from." > > In either case that tablespace becomes the default for the db and shows > up as NULL in pg_tables. > > > PgAdmin seems has the proper query to get the db and table tablespace names > > (right click table/select properties), but the queries I've used from > > various sources like stackoverflow don't provide the correct named > > tablespace. > > It is probably doing something like: > > SELECT > datname, spcname > FROM > pg_database AS pd > JOIN > pg_tablespace AS pt > ON > pd.dattablespace = pt.oid; > > > Thanks, > > Alex > > Sent with ProtonMail Secure Email. > > -- > > Adrian Klaver > adrian.kla...@aklaver.com
Re: How to keep format of views source code as entered?
Hi Ingolf, For comments in views, I create a unused CTE and put my comments there, e.g. WITH v_comments AS ( SELECT 'this is my comment' AS comment ) Alex Sent with [ProtonMail](https://protonmail.com) Secure Email. ‐‐‐ Original Message ‐‐‐ On Thursday, January 7, 2021 11:19 AM, Markhof, Ingolf wrote: > Hi! > > Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL > code the system returns when I open a views source code is different from the > code I entered. The code is formatted differently, comments are gone and e.g. > all text constants got an explicit cast to ::text added. (see sample below). > > I want the SLQ code of my views stored as I entered it. Is there any way to > achieve this? Or will I be forced to maintain my views SQL code outside of > PostgreSQL views? > > Any hints welcome! > > Here is an example: > > I enter this code to define a simple view: > > createorreplaceview myview as > > select > > product_id, > > product_acronym > > from > > products -- my comment here > > where > > product_acronym = 'ABC' > > ; > > However, when I open the view my SQL client (DBeaver) again, this is what I > get: > > CREATEORREPLACEVIEW myview > > ASSELECT product_id, > > product_acronym > > FROM products > > WHERE product_acronym = 'ABC'::text; > > So, the formatting changed, keywords are capitalized, the comment I added in > the from-part has gone and the text constant 'ABC' changed to 'ABC'::text. > > Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - > Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - > Vorsitzender des Aufsichtsrats: Francesco de Maio
Re: How to keep format of views source code as entered?
Ugh, I wasn't and just tried it, thanks. I've saved comments before on pgadmin and wasn't aware it using a sql statement to save it, I thought it was local to my environment. Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Tuesday, January 12, 2021 4:17 PM, Alban Hertroys wrote: > > On 12 Jan 2021, at 20:54, Alex Williams valencesh...@protonmail.com wrote: > > Hi Ingolf, > > For comments in views, I create a unused CTE and put my comments there, e.g. > > WITH v_comments AS ( > > SELECT 'this is my comment' AS comment > > ) > > Alex > > You do know about COMMENT ON VIEW v_comments IS ’this is my comment’, right? > > > ‐‐‐ Original Message ‐‐‐ > > On Thursday, January 7, 2021 11:19 AM, Markhof, Ingolf > > ingolf.mark...@de.verizon.com wrote: > > > > > Hi! > > > Switching from Oracle SLQ to PostgreSQL I am facing the issue that the > > > SQL code the system returns when I open a views source code is different > > > from the code I entered. The code is formatted differently, comments are > > > gone and e.g. all text constants got an explicit cast to ::text added. > > > (see sample below). > > > I want the SLQ code of my views stored as I entered it. Is there any way > > > to achieve this? Or will I be forced to maintain my views SQL code > > > outside of PostgreSQL views? > > > Any hints welcome! > > > Here is an example: > > > I enter this code to define a simple view: > > > create or replace view myview as > > > select > > > product_id, > > > product_acronym > > > from > > > products -- my comment here > > > where > > > product_acronym = 'ABC' > > > ; > > > However, when I open the view my SQL client (DBeaver) again, this is what > > > I get: > > > CREATE OR REPLACE VIEW myview > > > AS SELECT product_id, > > > > > > product_acronym > > > > > > > > > FROM products > > > WHERE product_acronym = 'ABC'::text; > > > So, the formatting changed, keywords are capitalized, the comment I added > > > in the from-part has gone and the text constant 'ABC' changed to > > > 'ABC'::text. > > > Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - > > > Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - > > > Vorsitzender des Aufsichtsrats: Francesco de Maio > > Alban Hertroys > > - > > There is always an exception to always.
