Thread: Regarding template1 database

On 04/22/2013 07:48 PM, S H wrote:
> I would like some assistance in understanding of template1 database.
> - I want to know the tables of tempate1

There are none, unless you choose to add some.

> - I want to know when there is any insertion/updation in template1
> theoretically.

You could if you create a table.

> - If i want to monitor the transactions done in the template1 database (
> insertion/updation ) , is there any way to track the same.

You would need to write something like a trigger function or turn up the
logging and parse the logs.


Might want to review the template database docs to see how templates are
intended to be used, generally not as an 'active' database:

http://www.postgresql.org/docs/9.2/static/manage-ag-templatedbs.html

>
> ~SH
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 4/22/2013 8:08 PM, S H wrote:
> I want to monitor externally, without changing the database. If there
> are some system queries which can tell me what are the number of
> database insertion/updation in the template1 ( including system table
> it will be helpful for me).

the system tables include the shared catalogs like pg_roles, which are
common with all databases and are modified by ALTER ROLE etc, and the
pg_catalog tables like pg_classes, which get modified when you do things
like CREATE TABLE.  it also includes system tables like
pg_stat_activity, which contain views into postgres's innards, and are
never directly modified, just queried.

so again, your request doesn't make much sense.


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 04/22/2013 08:08 PM, S H wrote:
>> > - I want to know the tables of tempate1
>>
>> There are none, unless you choose to add some.
>
> I got it there are no tables. What about the system tables of template1
> database ? Are they updated or not?
>
>  > > - If i want to monitor the transactions done in the template1
> database (
>  > > insertion/updation ) , is there any way to track the same.
> I want to monitor externally, without changing the database. If there
> are some system queries which can tell me what are the number of
> database insertion/updation in the template1 ( including system table it
> will be helpful for me).

As mentioned earlier the template databases are not really supposed to
be used as active databases, but as templates for creating new
databases. Also, as John mentioned system tables/views are often global
and do not apply to a particular database. It would be helpful to
explain what it is you want to accomplish. For example, this is for
security purposes and I want to know if someone is using template1.

--
Adrian Klaver
adrian.klaver@gmail.com

On 4/24/13, S H <msq001@live.com> wrote:
> I am working on some legacy product. In which template1 vacuuming with
> Analyze is executed through cron job every one hour and some times it is
> taking lot of time.I want to know if there is any major update/insert in
> template1 at some time, so i wanted to monitor the number of insertions and
> deletions done in template1.

Sounds like you have used "template1" as your live database.
OK, so from your point of view, template1 IS your database.

In that case, perhaps google for "postgresql transaction monitoring"
or similar. Others might be able to give you better advice/links.

Good luck,
Zenaan

On 04/23/2013 07:26 AM, S H wrote:
> I am working on some legacy product. In which template1 vacuuming with
> Analyze is executed through cron job every one hour and some times it is
> taking lot of time.
> I want to know if there is any major update/insert in template1 at some
> time, so i wanted to monitor the number of insertions and deletions done
> in template1.

What version of Postgres are you using? If it is new enough autovacuum
was created to deal with your situation.


--
Adrian Klaver
adrian.klaver@gmail.com

... template1 vacuuming with Analyze is executed through cron job every one hour and some times it is taking lot of time

template1=# \l
                                       List of databases
      Name      |    Owner    | Encoding  |   Collate   |    Ctype    |   Access privileges   
----------------+-------------+-----------+-------------+-------------+------------------------
 blogs          | blogs       | UTF8      | en_US.UTF-8 | en_US.UTF-8 |
 junk           | postgres    | UTF8      | en_US.UTF-8 | en_US.UTF-8 |
 pierce         | pierce      | UTF8      | en_US.UTF-8 | en_US.UTF-8 | pierce=CTc/pierce     +
                |             |           |             |             | =Tc/pierce
 template0      | postgres    | UTF8      | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
                |             |           |             |             | postgres=CTc/postgres
 template1      | postgres    | UTF8      | en_US.UTF-8 | en_US.UTF-8 | postgres=C*T*/postgres+
                |             |           |             |             | =c/postgres
(5 rows)

    template1=#

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 04/24/2013 04:57 AM, S H wrote:
>      $ sudo -u postgres psql template1
>      psql (9.2.4)
>      Type "help" for help.
>
>      template1=# \d
>      No relations found.
>
> I am getting as mentioned above. I am not sure what is taking vacuuming
> long time.

If you are getting 'No relations found' then there is nothing in template1.

 From psql try:

\l+

This will show a list of databases in the cluster and their sizes.

>
> - What is the recommendation of vacuuming for wraparound issue for
> template1 - Once in month/week/year?
>
> - Is it required to run analyze on template1 and postgres database
> (system databases).
>
> Since it is already in production i can maximum change the interval and
> vacuum options and nothing more...


--
Adrian Klaver
adrian.klaver@gmail.com

On 04/24/2013 06:48 PM, S H wrote:
> What should be the interval for calling vacuum on template1 and postgres
> database in case
>
> 1. No table is created on template1.
>
> Should i pass analyze parameter for template1 and postgres vacuum.

I would suggest reading this section of the docs:

http://www.postgresql.org/docs/9.2/interactive/routine-vacuuming.html

If anything is still unclear afterwards feel free to ask follow up
questions.


--
Adrian Klaver
adrian.klaver@gmail.com