Tag: administration

Waiting for PostgreSQL 14 – Add “pg_database_owner” default role.

On 26th of March 2021, Noah Misch committed patch:

Add "pg_database_owner" default role.
 
Membership consists, implicitly, of the current database owner.  Expect
use in template databases.  Once pg_database_owner has rights within a
template, each owner of a database instantiated from that template will
exercise those rights.
 
Reviewed by John Naylor.
 
Discussion: https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com

Continue reading Waiting for PostgreSQL 14 – Add “pg_database_owner" default role.

Waiting for PostgreSQL 13 – Allow pg_stat_statements to track planning statistics.

On 2nd of April 2020, Fujii Masao committed patch:

Allow pg_stat_statements to track planning statistics.
 
This commit makes pg_stat_statements support new GUC
pg_stat_statements.track_planning. If this option is enabled,
pg_stat_statements tracks the planning statistics of the statements,
e.g., the number of times the statement was planned, the total time
spent planning the statement, etc. This feature is useful to check
the statements that it takes a long time to plan. Previously since
pg_stat_statements tracked only the execution statistics, we could
not use that for the purpose.
 
The planning and execution statistics are stored at the end of
each phase separately. So there are not always one-to-one relationship
between them. For example, if the statement is successfully planned
but fails in the execution phase, only its planning statistics are stored.
This may cause the users to be able to see different pg_stat_statements
results from the previous version. To avoid this,
pg_stat_statements.track_planning needs to be disabled.
 
This commit bumps the version of pg_stat_statements to 1.8
since it changes the definition of pg_stat_statements function.
 
Author: Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao
Reviewed-by: Sergei Kornilov, Tomas Vondra, Yoshikazu Imai, Haribabu Kommi, Tom Lane
Discussion: https://postgr.es/m/CAHGQGwFx_=DO-Gu-MfPW3VQ4qC7TfVdH2zHmvZfrGv6fQ3D-Tw@mail.gmail.com
Discussion: https://postgr.es/m/CAEepm=0e59Y_6Q_YXYCTHZkqOc6H2pJ54C_Xe=VFu50Aqqp_sA@mail.gmail.com
Discussion: https://postgr.es/m/DB6PR0301MB21352F6210E3B11934B0DCC790B00@DB6PR0301MB2135.eurprd03.prod.outlook.com

Continue reading Waiting for PostgreSQL 13 – Allow pg_stat_statements to track planning statistics.

Which tables should be auto vacuumed or auto analyzed – UPDATE

Some time ago I wrote blogpost which showed how to list tables that should be autovacuumed or autoanalyzed.

Query in there had one important problem – it didn't take into account per-table settings.

Continue reading Which tables should be auto vacuumed or auto analyzed – UPDATE

Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity

On 6th of February 2020, Michael Paquier committed patch:

Add leader_pid to pg_stat_activity
 
This new field tracks the PID of the group leader used with parallel
query.  For parallel workers and the leader, the value is set to the
PID of the group leader.  So, for the group leader, the value is the
same as its own PID.  Note that this reflects what PGPROC stores in
shared memory, so as leader_pid is NULL if a backend has never been
involved in parallel query.  If the backend is using parallel query or
has used it at least once, the value is set until the backend exits.
 
Author: Julien Rouhaud
Reviewed-by: Sergei Kornilov, Guillaume Lelarge, Michael Paquier, Tomas
Vondra
Discussion: https://postgr.es/m/CAOBaU_Yy5bt0vTPZ2_LUM6cUcGeqmYNoJ8-Rgto+c2+w3defYA@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity

Which tables should be auto vacuumed or auto analyzed?

Recently I was in a situation where autovacuum couldn't keep up with changes. To solve the problem I finally decided to manually vacuum analyze all tables (manual vacuum/analyze is faster than one ran by autovacuum daemon).

But it irritated me that I didn't have ready way to check which tables are waiting for autovacuum to work on them.

So, I wrote it.

Continue reading Which tables should be auto vacuumed or auto analyzed?

Which schema is using the most disk space?

I was faced with interesting problem. Which schema, in my DB, uses the most disk space? Theoretically it's trivial, we have set of helpful functions:

  • pg_column_size
  • pg_database_size
  • pg_indexes_size
  • pg_relation_size
  • pg_table_size
  • pg_tablespace_size
  • pg_total_relation_size

But in some cases it becomes more of a problem. For example – when you have thousands of tables …

Continue reading Which schema is using the most disk space?

I have PostgreSQL, loaded some data, and have app using it. Now what?

I had to deal with this question, or some version of it, quite a few times. So, decided to write a summary on what one could (or should) do, after data is in database, and application is running. Namely – setup some kind of replication and backups.

What to use, how, and why? This is what this post is all about.

Continue reading I have PostgreSQL, loaded some data, and have app using it. Now what?

Waiting for 9.3 – Add \watch [SEC] command to psql.

On 4th of April, Tom Lane committed patch:

Add \watch [SEC] command to psql.
 
This allows convenient re-execution of commands.
 
Will Leinweber, reviewed by Peter Eisentraut, Daniel Farina, and Tom Lane

Continue reading Waiting for 9.3 – Add \watch [SEC] command to psql.

How much RAM is PostgreSQL using?

(disclaimer: all the data and examples in here are on Linux – the same data can be probably obtained on other systems too, it's just that I work on Linux and don't know other systems well).

This question pops occasionally in various places – PostgreSQL is using too much memory, why is that, and how can it be mitigated?

Before we can go to “optimizing", we should understand the problem. But do we? Both standard tools – ps and top – lie. How/why? Let's see.

Continue reading How much RAM is PostgreSQL using?

How to let clients to create new users?

Some (quite long) time ago, someone, somewhere (my memory is pretty fragile) asked a question. I don't have it exact, but the gist was: is it possible to give some users rights to create new users, without making them superusers, and forcing new users to have access only to one particular database.

After some discussion it was clear that the scenario was shared hosting with PostgreSQL, so the situation could look like this:

you are administrator of shared hosting service. One of services is PostgreSQL. You have client, named “depesz", and you want him to be able to create new users, but these users shouldn't be able to connect to any other database than depesz's db.

Is it doable?

Continue reading How to let clients to create new users?