PostgreSQL template1

Classic Servoy
1

Hi All

I accidentally imported a dump into the template1 database. I found that because no tables where created in the database I expected the dump to go to. When looking further, I found I could make a new connection in the pgAdmin3 Query tool and found my tables there.

But as the template1 database serves as a template for making new databases, I should bring it into the “clean” state again (my understanding so far).

The manual says one should do it like this (after dropping the template1 database):

CREATE DATABASE template1 TEMPLATE template0;

Can anyone confirm this procedure is ok and will give me my original template1 database back?

Why don’t I see the template1 database in pgAdmin? Can I see it somehow?

I can make a (new) connection to the template1 database in the Query tool, but not to the template0 database. Is this by intention (of the postgres developers)?

Thanks for hints, Robert

2

Hi Robert,

Robert Huber:
But as the template1 database serves as a template for making new databases, I should bring it into the “clean” state again (my understanding so far).

The manual says one should do it like this (after dropping the template1 database):

CREATE DATABASE template1 TEMPLATE template0;

Can anyone confirm this procedure is ok and will give me my original template1 database back?

That is correct. template0 is there just for these types of mishaps.

Robert Huber:
Why don’t I see the template1 database in pgAdmin? Can I see it somehow?

PgAdmin simply filters them out. You can make them show up by ticking the ‘Show System Objects in treeview’ checkbox in the preferences (Display tab).

Robert Huber:
I can make a (new) connection to the template1 database in the Query tool, but not to the template0 database. Is this by intention (of the postgres developers)?

This is intentional. As you already know template1 is used when you create a new database.
So you could setup all kinds of languages, functions, casts, default tables, data, etc. in here that will be then directly available in your newly created database.
Template0 is there for when you screw up your template1 and therefore doesn’t allow direct access to it.

By the way, the TEMPLATE argument is also a nice way to duplicate your database with all it’s data. Can be very handy when you need a test environment with real data without the danger to screw it up :)

CREATE DATABASE myNewDB TEMPLATE myOldDB;

Hope this helps.

3

Hi Robert

Thanks for the very useful info! Very much appreciated!

If one gets a full test database with

CREATE DATABASE myTestDB TEMPLATE myProductionDB;

How do you easily change the serverName property on all forms in a solution (with modules) to point to the new DB?

Best regards, Robert

ROCLASI:
Hi Robert,

Robert Huber:
I can make a (new) connection to the template1 database in the Query tool, but not to the template0 database. Is this by intention (of the postgres developers)?

This is intentional. As you already know template1 is used when you create a new database.
So you could setup all kinds of languages, functions, casts, default tables, data, etc. in here that will be then directly available in your newly created database.
Template0 is there for when you screw up your template1 and therefore doesn’t allow direct access to it.

By the way, the TEMPLATE argument is also a nice way to duplicate your database with all it’s data. Can be very handy when you need a test environment with real data without the danger to screw it up :)

CREATE DATABASE myNewDB TEMPLATE myOldDB;

Hope this helps.

4

Hi Robert,

Robert Huber:
If one gets a full test database with

CREATE DATABASE myTestDB TEMPLATE myProductionDB;

How do you easily change the serverName property on all forms in a solution (with modules) to point to the new DB?

You don’t. :)
You simply change the database in the jdbc connection. Servoy itself refers to the connection name, not the database name in the solutions.

Hope this helps.

5

Hello Robert

How could I forget about that :-(

Thanks anyway! Tomorrow I am going with Birgit to Munich to the Servoy 4 Preview Meeting :-)

Regards, Robert

ROCLASI:
Hi Robert,

Robert Huber:
If one gets a full test database with

CREATE DATABASE myTestDB TEMPLATE myProductionDB;

How do you easily change the serverName property on all forms in a solution (with modules) to point to the new DB?

You don’t. :)
You simply change the database in the jdbc connection. Servoy itself refers to the connection name, not the database name in the solutions.

Hope this helps.

6

Hi Robert

I tried to make a test database as you suggested but get the error as seen in the attached picture. I use the Postgres Version 8.3.1. Any idea why Postgres thinks the database is not there altough it’s there?
Camel casing should not be a problem, should it?

Best regards, Robert

ROCLASI:
Hi Robert,

Robert Huber:
If one gets a full test database with

CREATE DATABASE myTestDB TEMPLATE myProductionDB;

How do you easily change the serverName property on all forms in a solution (with modules) to point to the new DB?

You don’t. :)
You simply change the database in the jdbc connection. Servoy itself refers to the connection name, not the database name in the solutions.

Hope this helps.

duplicating database.png
duplicating database.png878×663 74.1 KB

7

Hi Robert,

Robert Huber:
I tried to make a test database as you suggested but get the error as seen in the attached picture. I use the Postgres Version 8.3.1. Any idea why Postgres thinks the database is not there altough it’s there?
Camel casing should not be a problem, should it?

I suspect that PgAdmin sends those commands all lowercase to the server, at least the error message would suggest that. I don’t know because I tend to use psql for these things.
But if you use PgAdmin why don’t you just right-click on the databases node and select ‘New Database’.
This will give you a dialog where you also can select any database as template.

Hope this helps.

8

Hello Robert

Good idea, tried it, still no luck. I have o idea what other user is connected to the db, except pgAdmin itself, as I am the only user (on my MacBook Pro) and the db is local on this MacBook.
I assume in your environment the duplication of a database works? I first thought that may be I have to put the duplicate db into another tablespace, but the error message doesn’t seem to indicate that’s the problem.

Regards, Robert

ROCLASI:
Hi Robert,

Robert Huber:
I tried to make a test database as you suggested but get the error as seen in the attached picture. I use the Postgres Version 8.3.1. Any idea why Postgres thinks the database is not there altough it’s there?
Camel casing should not be a problem, should it?

I suspect that PgAdmin sends those commands all lowercase to the server, at least the error message would suggest that. I don’t know because I tend to use psql for these things.
But if you use PgAdmin why don’t you just right-click on the databases node and select ‘New Database’.
This will give you a dialog where you also can select any database as template.

Hope this helps.

9

Hi Robert

Now it’s getting a bit strange. I tried to duplicate the db in psql as you suggested, I get now error message there but also the db is NOT created at all, see attached screenshot.

Hmmm, such things seem always to happen me (other having only a beautiful development life .-)

With what user did you create a duplicated db?

Regards, Robert

ROCLASI:
Hi Robert,

Robert Huber:
I tried to make a test database as you suggested but get the error as seen in the attached picture. I use the Postgres Version 8.3.1. Any idea why Postgres thinks the database is not there altough it’s there?
Camel casing should not be a problem, should it?

I suspect that PgAdmin sends those commands all lowercase to the server, at least the error message would suggest that. I don’t know because I tend to use psql for these things.
But if you use PgAdmin why don’t you just right-click on the databases node and select ‘New Database’.
This will give you a dialog where you also can select any database as template.

Hope this helps.

psql trial to create a database.png
psql trial to create a database.png497×186 1.7 KB

10

Hi Robert,

Robert Huber:
I assume in your environment the duplication of a database works? I first thought that may be I have to put the duplicate db into another tablespace, but the error message doesn’t seem to indicate that’s the problem.

I tested it on an 8.3 and 8.2.5 machine and in both cases it works for me.

Robert Huber:
Now it’s getting a bit strange. I tried to duplicate the db in psql as you suggested, I get now error message there but also the db is NOT created at all, see attached screenshot.

Psql allows you to type multiple lines before it executes it. Just end your statement with a semi-colon and press enter to execute it.

Robert Huber:
With what user did you create a duplicated db?

Any user that can create databases and has (full) access to the template database.

11

Hi Robert

It was a CamelCase thing. As soon as I entered in psql

create database TrackITTest template “TrackIT”

it worked :-)
And I assume it will work in pgAdmin as well. As you say, PostgreSQL converts names to lower case, so the above leads to a trackittest database.

Thanks, Robert