1

I have started to learn about SQL and installed Valentina Studio & PSQL on my Ubuntu.

I have defined a password via sudo -u postgres psql postgres and then \password postgres.

Then I have entered Valentina Studio and connected to this server with 'discover localhost server- it found thePostgresSQL` and I entered the password that I set up before in the terminal and it looked ok and like it was connected.

I have created 4 empty new databases in the VStudio and tried to import to them some databases which are located on my desktop.

I got the database does not exist while it sure does as you can see in the screenshot. When I tried to do the same with template1 the skelton database of psql, it worked.

But why the Vstudio and the PSQL are not connected to each other?

enter image description here

Zoe
  • 27,060
  • 21
  • 118
  • 148
NoobCoder
  • 513
  • 3
  • 18
  • 1
    You specified `Employees` for `-d`, but the database in the server is misspelled as `Empolyees`. You also want to get rid of the objects you added to `template1` otherwise they will show up in any new databases you create as `template1` is the default template for new databases. – Adrian Klaver Oct 23 '21 at 14:48
  • oh man, I swear I looked for typos and did not see anything. Sorry for the trouble. How can I get rid of the objects that I added to `template1`? Can i just delete this database? I can't see it in the VStudio as well as you can see, I can see only the ones that were created by me. – NoobCoder Oct 23 '21 at 14:52
  • It will be easier to use `psql`(which is true of most things). At this point the easiest solution would be to recreate `template1`. See [Template DBs](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html). Look for the **NOTE** at the bottom. – Adrian Klaver Oct 23 '21 at 15:01

1 Answers1

1

Example of recreating template1 database in psql as postgres user.

update pg_database set datistemplate = 'f' where datname = 'template1';
drop database template1;
create database template1 template template0 is_template true ;
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • thank you. eventually I went with `pg_dump` and then `init db`. is there any cons to what I did? or my way and your way have the same effect? – NoobCoder Oct 23 '21 at 15:15
  • `initdb` will only work if you removed the old `PGDATA` directory, in which case it would create a new cluster with a clean `template1`. It is the nuclear option. What did you dump with `pg_dump` and did you use that or `pg_dumpall`? If `pg_dumpall` you put back the contents of the old `template1`. Best to verify what is in the new `template1`. – Adrian Klaver Oct 23 '21 at 15:19
  • the problem is I can''t see `template0` or `template1` in the `vstudio` I use.. but thanks I did what you told me. I did use 'pg_dumpall'. I thought that `dumpall` deletes all databases and then `initdb` creates the skelton dbs again (`template0` and `template1`) – NoobCoder Oct 23 '21 at 15:31
  • Don't use `vstudio` use `psql`. [pg_dumpall](https://www.postgresql.org/docs/14/app-pg-dumpall.html) backs up all the databases, with exception of `template0`, to a text file. If you then use that text file to restore to the new cluster you will get back the contents of the old `template1` database as it was when you dumped it, in your case with the objects you added. – Adrian Klaver Oct 23 '21 at 15:42
  • If you want to use `vstudio` to see system databases see this [Postgres preferences](https://valentina-db.com/docs/dokuwiki/v11/doku.php?id=valentina:products:vstudio:help:dialogs:preferences:postgresql_server) and check `Show System Databases` and `Show System Schemas`. – Adrian Klaver Oct 23 '21 at 16:21