Would you like to learn how to monitor a PostgreSQL server using Zabbix? In this tutorial, we are going to show you how to use Zabbix to monitor a PostgreSQL server.

Zabbix Playlist:

On this page, we offer quick access to a list of videos related to Zabbix installation.

Don’t forget to subscribe to our youtube channel named FKIT.

Configure a PostgreSQL user to Zabbix

• IP – 192.168.15.11
• Operational System – Ubuntu 19.10
• Hostname – POSTGRESQL

Zabbix requires an account to access the PostgreSQL server and perform the database monitoring.

On the PostgreSQL database server, create an account to Zabbix.

Give monitoring privilege over all PostgreSQL databases to the Zabbix user.

Copy to Clipboard

Take note of the PostgreSQL username and password created.

Locate and edit the pg_hba.conf configuration file.

Copy to Clipboard

Add the following lines at the beginning of this file.

Copy to Clipboard

Here is our pg_hba.conf file content:

Copy to Clipboard

Restart the PostgreSQL service.

Copy to Clipboard

Test if you are able to connect to the local PostgreSQL server using the zbx_monitor account.

The system should not ask for a password.

Copy to Clipboard

You have finished the PostgreSQL service configuration.

Install the Zabbix Agent on the PostgreSQL Server

• IP – 192.168.15.11
• Operational System – Ubuntu 19.10
• Hostname – POSTGRESQL

Now, we need to install the Zabbix agent on the computer running the PostgreSQL service.

On the Linux console, use the following commands to install the required packages.

Copy to Clipboard

Download the Zabbix installation package.

Copy to Clipboard

Extract the Zabbix installation package, compile and install the Zabbix agent.

Copy to Clipboard

Find the location of the zabbix_agentd.conf file on your system.

Edit the zabbix_agentd.conf file.

Copy to Clipboard

Here is the original file, before our configuration.

Copy to Clipboard

Here is the new file with our configuration.

Copy to Clipboard

In our example, the Zabbix agent is configured to allow the connection from the Zabbix server 192.168.15.10.

The Zabbix server with the IP address 192.168.15.10 is allowed to request and receive information from this agent.

The Localhost, 127.0.0.1, is allowed to request and receive information from the agent.

Create the following required directories.

Copy to Clipboard

Download the Zabbix source code from Github.

Copy to Clipboard

Copy the required files to monitor the PostgreSQL server from the Zabbix repository.

Copy to Clipboard

The Zabbix installation package comes with a service startup script.

Copy the startup script using the commands below.

Copy to Clipboard

You can now use the following commands to start the Zabbix agent service.

Copy to Clipboard

In our example, the Zabbix agent will monitor the PostgreSQL service installed on the localhost.

Restart the Zabbix Agent.

Copy to Clipboard

You have finished the Zabbix agent installation.

You can now use the Zabbix server dashboard to add this computer to the network monitoring service.

Zabbix Monitor PostgreSQL

Access the Zabbix server dashboard and add the Linux computer running PostgreSQL as a Host.

Open your browser and enter the IP address of your web server plus /zabbix.

In our example, the following URL was entered in the Browser:

• http://192.168.15.10/zabbix

On the login screen, use the default username and default password.

• Default Username: Admin
• Default Password: zabbix

zabbix login

After a successful login, you will be sent to the Zabbix Dashboard.

Zabbix 4.4 dashboard

On the dashboard screen, access the Configuration menu and select the Host option.

zabbix add host

On the top right of the screen, click on the Create host button.

Zabbix Create Host

Enter the following information:

• Host Name – Enter a Hostname to identify the Linux server running PostgreSQL.
• Visible Hostname – Repeat the hostname.
• Group – Select the name of a group to identify similar devices.
• Agent Interface – Enter the IP address of the Linux server.

Here is the original image, before our configuration.

zabbix linux - Antes

Here is the new image with our configuration.

zabbix monitor postgresql

Next, we need to associate the host with a specific network monitor template.

By default, Zabbix comes with a large variety of monitoring templates.

Access the Templates tab on the top of the screen.

Click on the Select button and locate the template named: Template DB PostgreSQL

zabbix monitoring postgresql

Click on the Add option.

Click on the Add button.

After a few minutes, you will be able to see the initial result on the Zabbix Dashboard.

In order to test your configuration, access the Monitoring menu and click on the Graphs option.

Zabbix Graphs Menu

On the top right of the screen, select the group named ALL.

Select your PostgreSQL computer hostname.

Select the graph named: PostgreSQL connections

monitor postgresql server

You should be able to see the graphic named PostgreSQL connections

postgresql monitor zabbix

Congratulations! You have configured the Zabbix server to monitor PostgreSQL.

Desde https://techexpert.tips/zabbix/monitor-postgresql-using-zabbix/

Many developers still install a database on their computer and work on it.
It may be a preferable method, but using Docker takes only seconds to set up a database. I used PostgreSQL and Docker in my latest project. As a result, I have achieved efficiency and convenience. I will give you my experience with PostgreSQL and Docker.
I can start with finding the Docker image we will use. There are two easy ways to do this. I prefer to visit the Docker Hub website. Alternatively, it can be used the command below. It will list you similar results to the Docker Hub.
docker search postgresql
I will use the postgres, the official image can be obtained in the search results. I prepared a Docker command with important and necessary parameters. It can be easily customized and used this command.
docker run --name postgresqldb -e POSTGRES_USER=myusername -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -v /data:/var/lib/postgresql/data -d postgres
It looks a little confused, but don’t worry. I will explain these parameters.
  • –name : I set a name for the container.
  • -e POSTGRES_USER : I set the database username.
  • -e POSTGRES_PASSWORD : I set the database password.
  • -p : 5432 port is used by default. But it can be changed this according to preference. Local port and container port can be set separately. The first port is for local, and the second is for container.
  • -v : This parameter is related to the Docker Volume feature. This is important for not losing data. With this feature, I synchronize with the folder containing the data in the database and a folder of my choice on the computer or server. When the container is closed or restarted for any reason, it can be easily started with the same data. The first path can be any folder you prefer. I set it to /data as an example. Please do not change the second path because data is in this path by default.
  • -d : I will run detached mode(background mode). If you run it without using it, the docker container will be stopped when you press the Ctrl + C combination or close the terminal screen. This is undesirable because the container should be running in the background.
  • postgres : Image name.
Let’s run the command together and see the result.
If you have encountered a similar output, you can be sure that everything is fine. You can see the working Docker containers by running the command below.
docker ps
You can also stop or restart your container using start and stop commands. In this example I set the container name as postgresqldb. You can run the start and stop commands with your own chosen name.
docker start postgresqldb
docker stop postgresqldb
You may think it took a long time because I explained the details. Don’t be biased, you can create a PostgreSQL database just by running a single Docker command.

 

Tomado de https://hackernoon.com/how-to-install-postgresql-with-docker-quickly-ki7g363m

I’ve installed 19.04 and removed dsnmasq, disabled systemd-resolve using

sudo systemctl disable systemd-resolved.service
sudo systemctl stop systemd-resolved

and want to use Network Manager to set manually my DNS servers. Now I want to configure a different DNS server, so I went to the network icon on the bar and clicked Wired -> Wired Settings -> Configure -> IPV4, select Automatic (DHCP) and disable DNS as automatic, putting my desired DNS servers. Even after rebooting, my /etc/resolv.conf file now only gets 127.0.0.53, not matter what I do on the Nettwork Manager configuration. Is there a way for the /etc/resolv.conf file to be configured by Network manager?

I’ve found a solution.

First, disable systemd-resolve:

sudo systemctl disable systemd-resolved.service
sudo systemctl stop systemd-resolved

Remove the symlink for resolv.conf and create a file:

rm -rf /etc/resolv.conf
touch /etc/resolv.conf
chmod a+rw /etc/resolv.conf

Now change the configuration file for Network Manager to restore the expected result: vi /etc/NetworkManager/NetworkManager.conf:

[main]
plugins=ifupdown,keyfile
# add this line
dns=default

and leave the rest of the file as is. Restart Network Manager:

sudo /etc/init.d/network-manager restart

and configure your preferred DNS servers. Done!

https://askubuntu.com/questions/1159084/make-dns-follow-configuration-from-network-manager-in-19-04

Aca mis pruebas, abajo la original:

yum install wal2json

create database daftest;
daftest=# CREATE TABLE test_table (
id char(10) NOT NULL,
code char(10),
PRIMARY KEY (id)
);


export PATH="$PATH:/usr/pgsql-12/bin"
pg_recvlogical
pg_recvlogical: error: no slot specified

pg_recvlogical -d daftest --slot test_slot --create-slot -P wal2json
pg_recvlogical -d daftest --slot test_slot --start -o pretty-print=1 -f - ## Va a quedar como esperando algo...

En una segunda terminal ejecuto: 
daftest=# insert into test_table (id,code) select 1, 'Hola';
INSERT 0 1

En la primera se ve:

{
"change": [
{
"kind": "insert",
"schema": "public",
"table": "test_table",
"columnnames": ["id", "code"],
"columntypes": ["character(10)", "character(10)"],
"columnvalues": ["1 ", "Hola "]
}
]
}

En la 2da: daftest=# update test_table set code = 'Chau' where id = '1';
UPDATE 1

En la 1ra:
{
"change": [
{
"kind": "update",
"schema": "public",
"table": "test_table",
"columnnames": ["id", "code"],
"columntypes": ["character(10)", "character(10)"],
"columnvalues": ["1 ", "Chau "],
"oldkeys": {
"keynames": ["id"],
"keytypes": ["character(10)"],
"keyvalues": ["1 "]
}
}
]
}

En la 2da: daftest=# delete from test_table where id = '1';
DELETE 1

En la 1ra:
{
"change": [
{
"kind": "delete",
"schema": "public",
"table": "test_table",
"oldkeys": {
"keynames": ["id"],
"keytypes": ["character(10)"],
"keyvalues": ["1 "]
}
}
]
}

ctrl+C
pg_recvlogical -d daftest --slot test_slot --drop-slot

Logical Decoding Output Plug-in Installation for PostgreSQL

This document describes the database setup required for streaming data changes out of PostgreSQL. This comprises configuration applying to the database itself as well as the installation of the wal2json logical decoding output plug-in. The installation and the tests are performed at the following environment/configuration:

Similar steps need to be taken for other Postgres and OS versions and the Decoderbufs logical decoding plug-in which also is supported by Debezium.

As of Debezium 0.10, the connector supports PostgreSQL 10+ logical replication streaming using pgoutput. This means that a logical decoding output plug-in is no longer necessary and changes can be emitted directly from the replication stream by the connector.

Logical Decoding Plug-ins

Logical decoding is the process of extracting all persistent changes to a database’s tables into a coherent, easy to understand format which can be interpreted without detailed knowledge of the database’s internal state.

As of PostgreSQL 9.4, logical decoding is implemented by decoding the contents of the write-ahead log, which describe changes on a storage level, into an application-specific form such as a stream of tuples or SQL statements. In the context of logical replication, a slot represents a stream of changes that can be replayed to a client in the order they were made on the origin server. Each slot streams a sequence of changes from a single database. The output plug-ins transform the data from the write-ahead log’s internal representation into the format the consumer of a replication slot desires. Plug-ins are written in C, compiled, and installed on the machine which runs the PostgreSQL server, and they use a number of PostgreSQL specific APIs, as described by the PostgreSQL documentation.

Debezium’s PostgreSQL connector works with one of Debezium’s supported logical decoding plug-ins,

to encode the changes in either Protobuf format or JSON format.

For simplicity, Debezium also provides a Docker image based on a vanilla PostgreSQL server image on top of which it compiles and installs the plug-ins.

The Debezium logical decoding plug-ins have only been installed and tested on Linux machines. For Windows and other platforms it may require different installation steps

Differences between Plug-ins

The plug-ins’ behaviour is not completely same for all cases. So far these differences have been identified

  • wal2json plug-in is not able to process quoted identifiers (issue)
  • wal2json plug-in does not emit events for tables without primary keys
  • wal2json plug-in does not support special values (NaN or infinity) for floating point types

All up-to-date differences are tracked in a test suite Java class.

More information about the logical decoding and output plug-ins can be found at:

Installation

At the current installation example, the wal2json output plug-in for logical decoding is used. The wal2json output plug-in produces a JSON object per transaction. All of the new/old tuples are available in the JSON object. The plug-in compilation and installation is performed by executing the related commands extracted from the Debezium docker image file.

Before executing the commands, make sure that the user has the privileges to write the wal2json library at the PostgreSQL lib directory (at the test environment, the directory is: /usr/pgsql-9.6/lib/). Also note that the installation process requires the PostgreSQL utility pg_config. Verify that the PATH environment variable is set so as the utility can be found. If not, update the PATH environment variable appropriately. For example at the test environment:

export PATH="$PATH:/usr/pgsql-9.6/bin"
wal2json installation commands
$ git clone https://github.com/eulerto/wal2json -b master --single-branch \
&& cd wal2json \
&& git checkout d2b7fef021c46e0d429f2c1768de361069e58696 \
&& make && make install \
&& cd .. \
&& rm -rf wal2json
wal2json installation output
Cloning into 'wal2json'...
remote: Counting objects: 445, done.
remote: Total 445 (delta 0), reused 0 (delta 0), pack-reused 445
Receiving objects: 100% (445/445), 180.70 KiB | 0 bytes/s, done.
Resolving deltas: 100% (317/317), done.
Note: checking out 'd2b7fef021c46e0d429f2c1768de361069e58696'.

You are in 'detached HEAD' state. You can look around, make experimental
changes and commit them, and you can discard any commits you make in this
state without impacting any branches by performing another checkout.

If you want to create a new branch to retain commits you create, you may
do so (now or later) by using -b with the checkout command again. Example:

  git checkout -b new_branch_name

HEAD is now at d2b7fef... Improve style
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o wal2json.o wal2json.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -L/usr/pgsql-9.6/lib -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags  -shared -o wal2json.so wal2json.o
/usr/bin/mkdir -p '/usr/pgsql-9.6/lib'
/usr/bin/install -c -m 755  wal2json.so '/usr/pgsql-9.6/lib/'

Installation on Fedora 30+

Debezium provides RPM package for Fedora operating system too. The package is updated always after a final Debezium release is done. To use the RPM in question just issue the standard Fedora installation command:

$ sudo dnf -y install postgres-decoderbufs

The rest of the configuration is same as described below for wal2json plugin.

PostgreSQL Server Configuration

Once the wal2json plug-in has been installed, the database server should be configured.

Setting up libraries, WAL and replication parameters

Add the following lines at the end of the postgresql.conf PostgreSQL configuration file in order to include the plug-in at the shared libraries and to adjust some WAL and streaming replication settings. The configuration is extracted from postgresql.conf.sample. You may need to modify it, if for example you have additionally installed shared_preload_libraries.

postgresql.conf , configuration file parameters settings
############ REPLICATION ##############
# MODULES
shared_preload_libraries = 'wal2json'   

# REPLICATION
wal_level = logical                     
max_wal_senders = 4                     
max_replication_slots = 4               
tells the server that it should load at startup the wal2json (use decoderbufs for protobuf) logical decoding plug-in(s) (the names of the plug-ins are set in protobuf and wal2json Makefiles)
tells the server that it should use logical decoding with the write-ahead log
tells the server that it should use a maximum of 4 separate processes for processing WAL changes
tells the server that it should allow a maximum of 4 replication slots to be created for streaming WAL changes

Debezium needs a PostgreSQL’s WAL to be kept during Debezium outages. If your WAL retention is too small and outages too long, then Debezium will not be able to recover after restart as it will miss part of the data changes. The usual indicator is an error similar to this thrown during the startup: ERROR: requested WAL segment 000000010000000000000001 has already been removed.

When this happens then it is necessary to re-execute the snapshot of the database. We also recommend to set parameter wal_keep_segments = 0. Please follow PostgreSQL official documentation for fine-tuning of WAL retention.

We strongly recommend reading and understanding the official documentation regarding the mechanics and configuration of the PostgreSQL write-ahead log.

Setting up replication permissions

Replication can only be performed by a database user that has appropriate permissions and only for a configured number of hosts. In order to give a user replication permissions, define a PostgreSQL role that has at least the REPLICATION and LOGIN permissions. For example:

CREATE ROLE name REPLICATION LOGIN;

Superusers have by default both of the above roles.

Add the following lines at the end of the pg_hba.conf PostgreSQL configuration file, so as to configure the client authentication for the database replication. The PostgreSQL server should allow replication to take place between the server machine and the host on which the Debezium PostgreSQL connector is running.

Note that the authentication refers to the database superuser postgres. You may change this accordingly, if some other user with REPLICATION and LOGIN permissions has been created.

pg_hba.conf , configuration file parameters settings
############ REPLICATION ##############
local   replication     postgres                          trust		
host    replication     postgres  127.0.0.1/32            trust		
host    replication     postgres  ::1/128                 trust		
tells the server to allow replication for postgres locally (i.e. on the server machine)
tells the server to allow postgres on localhost to receive replication changes using IPV4
tells the server to allow postgres on localhost to receive replication changes using IPV6

See the PostgreSQL documentation for more information on network masks.

Database Test Environment Set-up

For the testing purposes, a database named test with a table named test_table are created with the following DDL commands:

Database SQL commands for test database/table creation
CREATE DATABASE test;

CREATE TABLE test_table (
    id char(10) NOT NULL,
    code        char(10),
    PRIMARY KEY (id)
);

Decoding Output Plug-in Test

Test that the wal2json is working properly by obtaining the test_table changes using the pg_recvlogical PostgreSQL client application that controls PostgreSQL logical decoding streams.

Before starting make sure that you have logged in as a user with database replication permissions, as configured at a previous step. Otherwise, the slot creation and streaming fails with the following error message:

pg_recvlogical: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "[local]", user "root", SSL off

At the test environment, the user with replication permission is the postgres.

Also, make sure that the PATH environment variable is set so as the pg_recvlogical can be found. If not, update the PATH environment variable appropriately. For example at the test environment:

export PATH="$PATH:/usr/pgsql-9.6/bin"
  • Create a slot named test_slot for the database named test, using the logical output plug-in wal2json
$ pg_recvlogical -d test --slot test_slot --create-slot -P wal2json
  • Begin streaming changes from the logical replication slot test_slot for the database test
$ pg_recvlogical -d test --slot test_slot --start -o pretty-print=1 -f -
  • Perform some basic DML operations at test_table to trigger INSERT/UPDATE/DELETE change events
Interactive PostgreSQL terminal, SQL commands
test=# INSERT INTO test_table (id, code) VALUES('id1', 'code1');
INSERT 0 1
test=# update test_table set code='code2' where id='id1';
UPDATE 1
test=# delete from test_table where id='id1';
DELETE 1

Upon the INSERT, UPDATE and DELETE events, the wal2json plug-in outputs the table changes as captured by pg_recvlogical.

Output for INSERT event
{
  "change": [
    {
      "kind": "insert",
      "schema": "public",
      "table": "test_table",
      "columnnames": ["id", "code"],
      "columntypes": ["character(10)", "character(10)"],
      "columnvalues": ["id1       ", "code1     "]
    }
  ]
}
Output for UPDATE event
{
  "change": [
    {
      "kind": "update",
      "schema": "public",
      "table": "test_table",
      "columnnames": ["id", "code"],
      "columntypes": ["character(10)", "character(10)"],
      "columnvalues": ["id1       ", "code2     "],
      "oldkeys": {
        "keynames": ["id"],
        "keytypes": ["character(10)"],
        "keyvalues": ["id1       "]
      }
    }
  ]
}
Output for DELETE event
{
  "change": [
    {
      "kind": "delete",
      "schema": "public",
      "table": "test_table",
      "oldkeys": {
        "keynames": ["id"],
        "keytypes": ["character(10)"],
        "keyvalues": ["id1       "]
      }
    }
  ]
}

Note that the REPLICA IDENTITY of the table test_table is set to DEFAULT.

When the test is finished, the slot test_slot for the database test can be removed by the following command:

$ pg_recvlogical -d test --slot test_slot --drop-slot

REPLICA IDENTITY, is a PostgreSQL specific table-level setting which determines the amount of information that is available to logical decoding in case of UPDATE and DELETE events.

There are 4 possible values for REPLICA IDENTITY:

  • DEFAULTUPDATE and DELETE events will only contain the previous values for the primary key columns of a table
  • NOTHINGUPDATE and DELETE events will not contain any information about the previous value on any of the table columns
  • FULLUPDATE and DELETE events will contain the previous values of all the table’s columns
  • INDEX index nameUPDATE and DELETE events will contains the previous values of the columns contained in the index definition named index name

You can modify and check the replica REPLICA IDENTITY for a table with the following commands:

ALTER TABLE test_table REPLICA IDENTITY FULL;
test=# \d+ test_table
                         Table "public.test_table"
 Column |     Type      | Modifiers | Storage  | Stats target | Description
 -------+---------------+-----------+----------+--------------+------------
 id     | character(10) | not null  | extended |              |
 code   | character(10) |           | extended |              |
Indexes:
    "test_table_pkey" PRIMARY KEY, btree (id)
Replica Identity: FULL

Here is the output of wal2json plug-in on DELETE event and REPLICA IDENTITY set to FULL. Compare with the respective output when REPLICA IDENTITY is set to DEFAULT.

Output for `UPDATE`
{
  "change": [
    {
      "kind": "update",
      "schema": "public",
      "table": "test_table",
      "columnnames": ["id", "code"],
      "columntypes": ["character(10)", "character(10)"],
      "columnvalues": ["id1       ", "code2     "],
      "oldkeys": {
        "keynames": ["id", "code"],
        "keytypes": ["character(10)", "character(10)"],
        "keyvalues": ["id1       ", "code1     "]
      }
    }
  ]
}


https://debezium.io/documentation/reference/0.10/postgres-plugins.html

Coleccion de cositas para el .psqlrc

--------------------------------------------------
-- 20200115 DAF agrego funcionalidades
-- 20200108 DAF .psqlrc  Version inicial
--------------------------------------------------


-- Para que no muestre el output de los siquientes comandos
\set QUIET ON


-- Prompts
-- Prompt1 / Prompt2 / Prompt3
-- %M : server
-- %m : host name
-- %> : port
-- %n : user
-- %/ : current database
-- %~ : like %/ but ~ for default database
-- %# : '#' if superuser, '>' otherwise
-- %R
-- %x
-- %[...%] : terminal control characters
-- Examples

\set PROMPT1 'u: %n db: %/ %# '
\set PROMPT2 '%# '
\set PROMPT3 ''


\timing on

-- Pone en mayusculas las palabras autocompletadas
\set COMP_KEYWORD_CASE upper

--tipo de lineas, unicode, ascii etc
--\pset linestype ascii

-- Borde de la tabla de resultados
\pset border 0

-- unicode_border_linestyle: border drawing style for unicode style.
-- Values: single, double.
\pset unicode_border_linestyle single

-- unicode_column_linestyle: column drawing style for unicode style.
-- Values: single, double.
\pset unicode_column_linestyle single

-- unicode_header_linestyle: header drawing style for unicode style.
-- Values: single, double.
\pset unicode_header_linestyle single


-- columns: target width for wrapped format
-- Values: number
\pset columns 0

-- expanded: extended display
-- Values: auto, on, off
\x off


-- fieldsep_zero: set field separator to a zero byte.
--Values: no value.
--\pset fieldsep_zero 


-- fieldsep_zero: set field separator to a zero byte.
--Values: no value.
--\pset fieldsep_zero

-- recordsep_zero: set the line separator in unaligned output format to
-- zero byte.
-- Values: no values.
--\pset recordsep_zero

-- tableattr: attributes in the table tag for HTML format.
-- Values: string. Without value, will unset attributes.
--\pset tableattr TODO

-- title: table title for printed tables.
-- Values: string. Without value, title is unset.

-- tuples_only: can also be abreviated with \t
-- Values: on, off. Without value, will toggle.
\pset tuples_only off


-- Para que no pagine
\pset pager off

-- Como va a imprimir los NULOS
\pset null '[null]'

-- Nombre del archivo history
\set HISTFILE ~/scripts/psqlhists/psql_history- :HOST - :PORT - :DBNAME - :USER

-- Cantidad de comandos en el history
\set HISTSIZE 5000

-- Sin duplicados
\set HISTCONTROL ignoredups

-- Nivel de debug
\set VERBOSITY verbose
\errverbose

-- como el @echo off para imprimir algun mensaje
\set ECHO_HIDDEN ON

\set QUIET OFF

\echo '\nCurrent Host Server Date Time : '`date` '\n'

\echo '\t:activity                 :cache_hit              :nspsize                  :tablesize1'
\echo '\t:activity1                :conninfo               :nspsize_pgs              :total_index_size'
\echo '\t:activity2                :dbsize                 :ps                       :unused_indexes'
\echo '\t:activity96               :idletxn                :queries                  :uptime'
\echo '\t:activitytho              :index_size             :seq_scans                :uselesscol'
\echo '\t:                         :index_usage            :settings                 :vacuuminfo'
\echo '\t:autovacs                 :index_usage_adv        :show_slow_queries        :waits'
\echo '\t:backends                 :kill_old_transactions  :spcsize                  :wrapstats'
\echo '\t:bloat                    :locks                  :statrelfilepath          :wrapstats_raw'
\echo '\t:bloat2                   :locks1                 :statrelfilepath_agg      :wrapstats_rels'
\echo '\t:blocking                 :long_running_queries   :statrelspcfilepath_agg   :wrapstats_spc'
\echo '\t:buffer_cache_hit_ratio   :missing_indexes        :tablesize'                

\echo '\n\t:menu -- Help Menu'
\echo '\t\h-- Help with SQL commands'
\echo '\t\?-- Help with psql commands'

\echo '\nDevelopment queries:\n'
\echo '\t:sp-- Current Search Path'
\echo '\t:clear -- Clear screen'
\echo '\t:ll -- List\n'


\set sp 'SHOW search_path;'
\set clear '\\! clear;'
\set ll '\\! ls -lrt'

\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'


\set menu '\\i ~/.psqlrc'



-- buffer cache hit ratio
\set buffer_cache_hit_ratio 'select datname, blks_hit::float/(blks_hit+blks_read) as hit_ratio from pg_stat_database where blks_read+blks_hit <> 0;'
\set cache_hit 'SELECT ''index hit rate'' AS name, (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT ''cache hit rate'' AS name,       sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio FROM pg_statio_user_tables;'

-- kill transactions that have been "idle in transaction" for more than 10 minutes
\set kill_old_transactions 'select count(pg_terminate_backend(pid)) as nb_killed_proc from pg_stat_activity where query = \' in transaction\' and current_timestamp - query_start > \'10 min\';'

-- running queries
\set queries  'select current_timestamp - query_start as runtime,pid,datname,usename,query from pg_stat_activity where query != \'\' order by 1 desc; '



\set autovacs 'WITH q0_0 AS  (SELECT relid,          CASE              WHEN coalesce(last_vacuum, last_analyze, last_autovacuum, last_autoanalyze) IS NULL THEN NULL              WHEN last_vacuum =                     (SELECT MAX(UNNEST)                      FROM                        (SELECT UNNEST(ARRAY[last_vacuum,                                             last_analyze,                                             last_autovacuum,                                             last_autoanalyze]))s0_0) THEN '' v ''              WHEN last_analyze =                     (SELECT MAX(UNNEST)                      FROM                        (SELECT UNNEST(ARRAY[last_vacuum,                                             last_analyze,                                             last_autovacuum,                                             last_autoanalyze]))s1_0) THEN '' z ''              WHEN last_autoanalyze =                     (SELECT MAX(UNNEST)                      FROM                        (SELECT UNNEST(ARRAY[last_vacuum,                                             last_analyze,                                             last_autovacuum,                                             last_autoanalyze]))s2_0) THEN ''az ''              WHEN last_autovacuum =                     (SELECT MAX(UNNEST)                      FROM                        (SELECT UNNEST(ARRAY[last_vacuum,                                             last_analyze,                                             last_autovacuum,                                             last_autoanalyze]))s3_0) THEN ''av ''          END AS last_stats_str,          CASE              WHEN coalesce(last_vacuum, last_analyze, last_autovacuum, last_autoanalyze) IS NULL THEN NULL              WHEN last_vacuum =                     (SELECT MAX(UNNEST)                      FROM                        (SELECT UNNEST(ARRAY[last_vacuum,                                             last_analyze,                                             last_autovacuum,                                             last_autoanalyze]))s0_0) THEN last_vacuum::TIMESTAMP              WHEN last_analyze =                     (SELECT MAX(UNNEST)                      FROM                        (SELECT UNNEST(ARRAY[last_vacuum,                                             last_analyze,                                             last_autovacuum,                                             last_autoanalyze]))s1_0) THEN last_analyze::TIMESTAMP              WHEN last_autoanalyze =                     (SELECT MAX(UNNEST)                      FROM                        (SELECT UNNEST(ARRAY[last_vacuum,                                             last_analyze,                                             last_autovacuum,                                             last_autoanalyze]))s2_0) THEN last_autoanalyze::TIMESTAMP              WHEN last_autovacuum =                     (SELECT MAX(UNNEST)                      FROM                        (SELECT UNNEST(ARRAY[last_vacuum,                                             last_analyze,                                             last_autovacuum,                                             last_autoanalyze]))s3_0) THEN last_autovacuum::TIMESTAMP          END AS last_stats_stamp   FROM pg_stat_user_tables),     q0 AS  (SELECT relid::regclass rel,          n_tup_ins ti,          n_tup_upd + n_tup_del tm,          n_live_tup tliv,          n_dead_tup nded,          n_mod_since_analyze tmod,          last_stats_str||last_stats_stamp AS last_stats,          autoanalyze_count naz,          autovacuum_count nav,          analyze_count nz,          vacuum_count nv   FROM pg_stat_user_tables   JOIN q0_0 USING (relid)   ORDER BY last_stats_stamp DESC nulls LAST,                                        schemaname,                                        relname)SELECT * FROM q0 ;'

\set bloat2 'SELECT tablename AS TABLE_NAME,       ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat,       CASE           WHEN relpages < otta THEN ''0''           ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint)       END AS table_waste,       iname AS index_name,       ROUND(CASE WHEN iotta=0             OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat,       CASE           WHEN ipages < iotta THEN ''0''           ELSE pg_size_pretty((bs*(ipages-iotta))::bigint)       END AS index_waste FROM  (SELECT schemaname,          tablename,          cc.reltuples,          cc.relpages,          bs,          CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,          COALESCE(c2.relname,''?'') AS iname,          COALESCE(c2.reltuples,0) AS ituples,          COALESCE(c2.relpages,0) AS ipages,          COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta   FROM     (SELECT ma,             bs,             schemaname,             tablename,             (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,             (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2      FROM        (SELECT schemaname,                tablename,                hdr,                ma,                bs,                SUM((1-null_frac)*avg_width) AS datawidth,                MAX(null_frac) AS maxfracsum,                hdr+           (SELECT 1+count(*)/8            FROM pg_stats s2            WHERE null_frac<>0              AND s2.schemaname = s.schemaname              AND s2.tablename = s.tablename) AS nullhdr         FROM pg_stats s,           (SELECT              (SELECT current_setting(''block_size'')::numeric) AS bs,                   CASE WHEN substring(v,12,3) IN (''8.0'',                                                   ''8.1'',                                                   ''8.2'') THEN 27 ELSE 23 END AS hdr,                                                                                 CASE WHEN v ~ ''mingw32'' THEN 8 ELSE 4 END AS ma            FROM              (SELECT version() AS v) AS foo) AS constants         GROUP BY 1,                  2,                  3,                  4,                  5) AS foo) AS rs   JOIN pg_class cc ON cc.relname = rs.tablename   JOIN pg_namespace nn ON cc.relnamespace = nn.oid   AND nn.nspname = rs.schemaname   AND nn.nspname <> ''information_schema''   LEFT JOIN pg_index i ON indrelid = cc.oid   LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml ORDER BY CASE             WHEN relpages < otta THEN 0             ELSE bs*(sml.relpages-otta)::bigint         END DESC;'

\set bloat 'SELECT tablename AS TABLE_NAME,       ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat,       CASE           WHEN relpages < otta THEN ''0''           ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint)       END AS table_waste,       iname AS index_name,       ROUND(CASE WHEN iotta=0             OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat,       CASE           WHEN ipages < iotta THEN ''0''           ELSE pg_size_pretty((bs*(ipages-iotta))::bigint)       END AS index_waste FROM  (SELECT schemaname,          tablename,          cc.reltuples,          cc.relpages,          bs,          CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,          COALESCE(c2.relname,''?'') AS iname,          COALESCE(c2.reltuples,0) AS ituples,          COALESCE(c2.relpages,0) AS ipages,          COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta   FROM     (SELECT ma,             bs,             schemaname,             tablename,             (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,             (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2      FROM        (SELECT schemaname,                tablename,                hdr,                ma,                bs,                SUM((1-null_frac)*avg_width) AS datawidth,                MAX(null_frac) AS maxfracsum,                hdr+           (SELECT 1+count(*)/8            FROM pg_stats s2            WHERE null_frac<>0              AND s2.schemaname = s.schemaname              AND s2.tablename = s.tablename) AS nullhdr         FROM pg_stats s,           (SELECT              (SELECT current_setting(''block_size'')::numeric) AS bs,                   CASE WHEN substring(v,12,3) IN (''8.0'',                                                   ''8.1'',                                                   ''8.2'') THEN 27 ELSE 23 END AS hdr,                                                                                 CASE WHEN v ~ ''mingw32'' THEN 8 ELSE 4 END AS ma            FROM              (SELECT version() AS v) AS foo) AS constants         GROUP BY 1,                  2,                  3,                  4,                  5) AS foo) AS rs   JOIN pg_class cc ON cc.relname = rs.tablename   JOIN pg_namespace nn ON cc.relnamespace = nn.oid   AND nn.nspname = rs.schemaname   AND nn.nspname <> ''information_schema''   LEFT JOIN pg_index i ON indrelid = cc.oid   LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml ORDER BY CASE             WHEN relpages < otta THEN 0             ELSE bs*(sml.relpages-otta)::bigint         END DESC;'

\set blocking 'SELECT bl.pid AS blocked_pid,       ka.query AS blocking_statement,       now() - ka.query_start AS blocking_duration,       kl.pid AS blocking_pid,       a.query AS blocked_statement,       now() - a.query_start AS blocked_duration FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid JOIN pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid WHERE NOT bl.granted;'



\set idletxn 'SELECT datname,       pid,       (clock_timestamp() - backend_start) AS bk_age,       (clock_timestamp() - state_change) state_age,       (clock_timestamp() - xact_start) xact_age,       usename,       application_name app,       client_addr,       (STATE || CASE                     WHEN waiting THEN ''(w)''                     ELSE ''''                 END) AS STATE,       queryFROM pg_stat_activityWHERE xact_start IS NOT NULL  AND pid <> pg_backend_pid()ORDER BY xact_start;'

\set index_size 'SELECT relname AS name,       pg_size_pretty(sum(relpages*1024)) AS SIZEFROM pg_classWHERE reltype=0GROUP BY relnameORDER BY sum(relpages) DESC;'

\set index_usage_adv 'SELECT *FROM  (SELECT stat.relname AS TABLE,          stai.indexrelname AS INDEX,          CASE stai.idx_scan              WHEN 0 THEN ''Insufficient data''              ELSE (100 * stai.idx_scan / (stat.seq_scan + stai.idx_scan))::text || ''%''          END hit_rate,              CASE stat.idx_scan                  WHEN 0 THEN ''Insufficient data''                  ELSE (100 * stat.idx_scan / (stat.seq_scan + stat.idx_scan))::text || ''%''              END all_index_hit_rate,                  ARRAY     (SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)      FROM generate_subscripts(idx.indkey, 1) AS k      ORDER BY k) AS cols,                  stat.n_live_tup rows_in_table   FROM pg_stat_user_indexes AS stai   JOIN pg_stat_user_tables AS stat ON stai.relid = stat.relid   JOIN pg_index AS idx ON (idx.indexrelid = stai.indexrelid)) AS sub_innerORDER BY rows_in_table DESC,         hit_rate ASC;'

\set index_usage 'SELECT relname,       CASE idx_scan           WHEN 0 THEN ''Insufficient data''           ELSE (100 * idx_scan / (seq_scan + idx_scan))::text       END percent_of_times_index_used,       n_live_tup rows_in_tableFROM pg_stat_user_tables ORDER BY n_live_tup DESC;'

\set locks 'SELECT pg_stat_activity.pid,       pg_class.relname,       pg_locks.transactionid,       pg_locks.granted,       substring(pg_stat_activity.query                 FROM ''([^]*?){1,3}'') AS query_snippet,       age(now(),pg_stat_activity.query_start) AS "age"FROM pg_stat_activity,     pg_locksLEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)WHERE pg_stat_activity.query <> ''''  AND pg_locks.pid=pg_stat_activity.pid  AND pg_locks.mode = ''ExclusiveLock''ORDER BY query_start;'


\set locks1  'SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid JOIN pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid WHERE NOT bl.granted;'


\set long_running_queries 'SELECT pid,       now() - pg_stat_activity.query_start AS duration,       query AS queryFROM pg_stat_activityWHERE pg_stat_activity.query <> ''''::text  AND now() - pg_stat_activity.query_start > interval ''5 minutes''ORDER BY now() - pg_stat_activity.query_start DESC;'

\set missing_indexes 'SELECT relname,       seq_scan-idx_scan AS too_much_seq,       CASE           WHEN seq_scan-idx_scan > 0 THEN ''Missing Index?''           ELSE ''OK''       END,       pg_relation_size(relname::regclass) AS rel_size,       seq_scan,       idx_scanFROM pg_stat_all_tablesWHERE schemaname=''public''  AND pg_relation_size(relname::regclass) > 80000ORDER BY too_much_seq DESC;'

\set nspsize_pgs 'SELECT * from (SELECT    nspname,    pg_size_pretty(sum(8192::bigint*nullif(tbl.relpages, 0))) "tables",    pg_size_pretty(sum(8192::bigint*nullif(idx.relpages, 0))) "indexes",    pg_size_pretty(sum(8192::bigint*nullif(tst.relpages, 0))) "toast",    pg_size_pretty(sum(8192::bigint*(        coalesce(tbl.relpages, 0)      + coalesce(idx.relpages, 0)      + coalesce(tst.relpages, 0)))) AS "total",    concat(       count(distinct tbl.oid) FILTER (where tbl.relkind=''r'')::text,       ''/'',       count(distinct tst.oid)::text,       ''/'',       count(distinct idx.oid)::text,       ''/'',       count(distinct tbl.oid) FILTER (where tbl.relkind=''m'')::text    ) AS "#r/t/i/m"FROM    pg_class AS tblJOIN    pg_namespace AS nsp ON (tbl.relnamespace=nsp.oid)LEFT JOIN    pg_class tst ON (tbl.reltoastrelid=tst.oid)LEFT JOIN    pg_index ON (pg_index.indrelid=tbl.oid)LEFT JOIN    pg_class idx ON pg_index.indexrelid=idx.oidWHERE    (tbl.relkind = ''r'' OR tbl.relkind = ''m'')GROUP BY    nspnameORDER BY    sum(coalesce(tbl.relpages, 0)+coalesce(idx.relpages, 0)+coalesce(tst.relpages, 0))DESC) _'

\set nspsize 'SELECT * from (SELECT    nspname,    pg_size_pretty(sum(pg_table_size(pg_class.oid))) "Schema Size",    pg_size_pretty(sum(pg_indexes_size(pg_class.oid))) "Indexes",    count(pg_class.oid) "Tables"FROM pg_classJOIN pg_namespace ON (pg_class.relnamespace=pg_namespace.oid)WHERE relkind = ''r'' or relkind = ''m''GROUP BY nspnameORDER BY sum(pg_total_relation_size(pg_class.oid)) DESC) _'

\set ps 'SELECT pid,       application_name AS SOURCE,       age(now(),query_start) AS running_for,       waiting,       query AS queryFROM pg_stat_activityWHERE query <> ''''  AND STATE <> ''idle''  AND pid <> pg_backend_pid()ORDER BY 3 DESC;'

\set seq_scans 'SELECT relname AS name,       seq_scan AS COUNTFROM pg_stat_user_tables ORDER BY seq_scan DESC;'


-- number of connected backends
\set settings 'select name, setting,unit,context from pg_settings;'

\set show_slow_queries 'SELECT (total_time / 1000 / 60) AS total_minutes,       (total_time/calls) AS average_time,       queryFROM pg_stat_statementsORDER BY 1 DESC LIMIT 100;'

\set spcsize 'SELECT    coalesce(spcname, ''[Default]'') "Tablespace",    pg_size_pretty(sum(pg_relation_size(c.oid)) FILTER (WHERE relkind = ''r'' or relkind = ''m'')) "Data Size",    pg_size_pretty(sum(pg_relation_size(c.oid)) FILTER (WHERE relkind = ''i''                 )) "Index Size",    count(c.oid) "# Tables"FROM pg_class cLEFT JOIN pg_tablespace spc ON (c.reltablespace=spc.oid)WHERE relkind = ''r'' or relkind = ''m'' or relkind = ''i''GROUP BY 1/*ORDER BY sum(pg_total_relation_size(c.oid)) DESC;*/ORDER BY 1;'

\set statrelfilepath_agg 'select * from (select relid,    nsp.nspname,    c.relname,    fpath,numsegs,tot_size,access_bnds,modification_bnds,change_bnds,creation_bnds,isdir from (SELECT c.oid relid,       relpages / segsz.seg_size AS numsegs,       pg_relation_filepath(c.oid) AS fpath,       sum(size) tot_size,       tstzrange(min(fstat.access), max(fstat.access), ''[]'') access_bnds,       tstzrange(min(fstat.modification), max(fstat.modification), ''[]'') modification_bnds,       tstzrange(min(fstat.change), max(fstat.change), ''[]'') change_bnds,       tstzrange(min(fstat.creation), max(fstat.creation), ''[]'') creation_bnds,       fstat.isdirFROM (SELECT setting::bigint seg_sizeFROM pg_settingsWHERE name = ''segment_size'') AS segsz,pg_class c,generate_series(0::bigint, relpages / segsz.seg_size) segnum,concat(pg_relation_filepath(c.oid), coalesce(''.''||nullif(segnum, 0), '''')) _fpath,pg_stat_file(_fpath) fstatwhere relkind=''r'' group by relid, numsegs, fstat.isdir)_JOIN pg_class c on c.oid=relidJOIN pg_namespace nsp ON c.relnamespace=nsp.oid)_'

\set statrelfilepath 'SELECT c.oid relid,       nsp.nspname,       c.relname,       segnum,       fpath,       fstat.size,       fstat.access,       fstat.modification,       fstat.change,       fstat.creation,       fstat.isdirFROM (SELECT setting::bigint seg_sizeFROM pg_settingsWHERE name = ''segment_size'') AS segsz,pg_class cJOIN pg_namespace nsp ON c.relnamespace=nsp.oid,generate_series(0::bigint, relpages / segsz.seg_size) segnum,concat(pg_relation_filepath(c.oid), coalesce(''.''||nullif(segnum, 0), '''')) fpath,pg_stat_file(fpath) fstat'

\set statrelspcfilepath_agg 'SELECTrelid,nspname,relname,spcname,fpath,numsegs,tot_size,access_bnds,modification_bnds,change_bnds,creation_bnds,isdirFROM (SELECT relid,    nsp.nspname,    c.relname,    spc.spcname,    fpath,    numsegs,    tot_size,    access_bnds,    modification_bnds,    change_bnds,    creation_bnds,    isdirFROM (SELECT c.oid relid,       1 + relpages/segsz.seg_size AS numsegs,       pg_relation_filepath(c.oid) AS fpath,       sum(size) tot_size,       tstzrange(min(fstat.access), max(fstat.access), ''[]'') access_bnds,       tstzrange(min(fstat.modification), max(fstat.modification), ''[]'') modification_bnds,       tstzrange(min(fstat.change), max(fstat.change), ''[]'') change_bnds,       tstzrange(min(fstat.creation), max(fstat.creation), ''[]'') creation_bnds,       fstat.isdirFROM (SELECT setting::bigint seg_sizeFROM pg_settingsWHERE name = ''segment_size'') AS segsz,pg_class c,generate_series(0::bigint, relpages / segsz.seg_size) segnum,concat(pg_relation_filepath(c.oid), coalesce(''.''||nullif(segnum, 0), '''')) _fpath,pg_stat_file(_fpath) fstatwhere relkind=''r'' group by relid, numsegs, fstat.isdir)_JOIN pg_class c    ON c.oid=relidJOIN pg_namespace nsp    ON c.relnamespace=nsp.oidJOIN pg_tablespace spc    ON ((c.reltablespace=spc.oid) OR (spc.spcname=''pg_default'' and c.reltablespace=0)))_'

\set total_index_size 'SELECT pg_size_pretty(sum(relpages*1024)) AS SIZEFROM pg_classWHERE reltype=0;'

\set unused_indexes 'SELECT schemaname || ''.'' || relname AS TABLE,       indexrelname AS INDEX,       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,       idx_scan AS index_scansFROM pg_stat_user_indexes uiJOIN pg_index i ON ui.indexrelid = i.indexrelidWHERE NOT indisunique  AND idx_scan < 50  AND pg_relation_size(relid) > 5 * 8192ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,                                                                         pg_relation_size(i.indexrelid) DESC;'


\set vacuuminfo '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/WITH table_opts AS (    SELECT      pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts    FROM      pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), storage_settings AS (    SELECT      oid, relname, nspname,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_threshold%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''autovacuum_vacuum_threshold'')::integer      END AS autovacuum_vacuum_threshold,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real      END AS autovacuum_vacuum_scale_factor,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_min_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''vacuum_freeze_min_age'')::integer      END AS autovacuum_freeze_min_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_table_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''vacuum_freeze_table_age'')::real      END AS autovacuum_freeze_table_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_max_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_freeze_max_age'')::real      END AS autovacuum_freeze_max_age    FROM      table_opts)SELECT  storage_settings.nspname AS schema,  storage_settings.relname AS table,  to_char(pg_class.reltuples, ''9G999G999G999'') AS rowcount,  to_char(psut.n_dead_tup, ''9G999G999G999'') AS dead_rowcount,  to_char(autovacuum_vacuum_threshold          + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), ''9G999G999G999'') AS autovacuum_threshold,  CASE    WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup      THEN ''yes''  END AS expect_autovacuum,  age(relfrozenxid) as relfrozenxid_age,  autovacuum_freeze_table_age,  CASE    WHEN age(relfrozenxid) > autovacuum_freeze_table_age      THEN ''yes''  END AS next_autovacuum_will_be_a_freeze,  autovacuum_freeze_max_age,  ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || ''%'' AS "% til forced vacuum freeze"FROM  pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid  INNER JOIN storage_settings ON pg_class.oid = storage_settings.oidORDER BY storage_settings.relname;'

\set waits 'SELECT pg_stat_activity.pid,       pg_stat_activity.query,       pg_stat_activity.state,       now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text  AND pg_stat_activity.waiting = TRUE;'

\set waits1 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.state, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;'


\set wrapstats_raw '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/select relid, spcid, spcname, nspid, nspname, reltuples, relpages, relsize_pgs, frozenage, iceinfrom (SELECT  storage_settings.oid AS relid,  spc.oid spcid,  spc.spcname,  nsp.oid nspid,  nsp.nspname,  c.reltuples,  c.relpages,  8192::bigint*c.relpages relsize_pgs,  age(relfrozenxid) as frozenage,  autovacuum_freeze_max_age - age(relfrozenxid) AS iceinFROM  pg_stat_user_tables psut  JOIN pg_class c ON psut.relid = c.oid  JOIN (    SELECT      oid, relname, nspname,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_threshold%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''autovacuum_vacuum_threshold'')::integer      END AS autovacuum_vacuum_threshold,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real      END AS autovacuum_vacuum_scale_factor,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_min_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''vacuum_freeze_min_age'')::integer      END AS autovacuum_freeze_min_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_table_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''vacuum_freeze_table_age'')::real      END AS autovacuum_freeze_table_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_max_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_freeze_max_age'')::real      END AS autovacuum_freeze_max_age    FROM      (    SELECT      pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts    FROM      pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid      )table_opts)storage_settings ON c.oid = storage_settings.oid  JOIN pg_tablespace spc on c.reltablespace=spc.oid or (c.reltablespace=0 and spc.spcname=''pg_default'')  JOIN pg_namespace nsp on c.relnamespace=nsp.oidORDER BY icein)_'

\set wrapstats_rels '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/WITH table_opts AS (    SELECT      pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts    FROM      pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), storage_settings AS (    SELECT      oid, relname, nspname,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_threshold%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''autovacuum_vacuum_threshold'')::integer      END AS autovacuum_vacuum_threshold,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real      END AS autovacuum_vacuum_scale_factor,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_min_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''vacuum_freeze_min_age'')::integer      END AS autovacuum_freeze_min_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_table_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''vacuum_freeze_table_age'')::real      END AS autovacuum_freeze_table_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_max_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_freeze_max_age'')::real      END AS autovacuum_freeze_max_age    FROM      table_opts),_ as (SELECT  storage_settings.oid::regclass AS "rel",  to_char(pg_class.reltuples, ''9G999G999G999'') AS "Rows",  to_char(pg_class.relpages, ''9G999G999'') AS "Pages",  pg_size_pretty(pg_table_size(pg_class.oid)) AS "Size",  pg_table_size(pg_class.oid) "size",  age(relfrozenxid) as "icedXID",  autovacuum_freeze_table_age "AV TblIceAge",  CASE    WHEN age(relfrozenxid) > autovacuum_freeze_table_age      THEN ''AV''    WHEN age(relfrozenxid) > current_setting(''vacuum_freeze_table_age'')::integer      THEN ''V''  END AS "Icing",  autovacuum_freeze_max_age "IceMaxAV",  autovacuum_freeze_max_age - age(relfrozenxid) AS "ice_in",  ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || ''%'' AS "IceAV%"FROM  pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid  INNER JOIN storage_settings ON pg_class.oid = storage_settings.oidORDER BY ice_in)select "rel", "Pages", "Size", "icedXID", ice_in, "IceAV%" from _'

\set wrapstats_spc '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/WITH table_opts AS (    SELECT      pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts    FROM      pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), storage_settings AS (    SELECT      oid, relname, nspname,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_threshold%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''autovacuum_vacuum_threshold'')::integer      END AS autovacuum_vacuum_threshold,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real      END AS autovacuum_vacuum_scale_factor,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_min_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''vacuum_freeze_min_age'')::integer      END AS autovacuum_freeze_min_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_table_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''vacuum_freeze_table_age'')::real      END AS autovacuum_freeze_table_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_max_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_freeze_max_age'')::real      END AS autovacuum_freeze_max_age    FROM      table_opts),_ as (SELECT  storage_settings.oid::regclass AS "rel",  spc.spcname,  to_char(c.reltuples, ''9G999G999G999'') AS "Rows",  to_char(c.relpages, ''9G999G999'') AS "Pages",  pg_size_pretty(8192::bigint*c.relpages) AS "Size",  8192::bigint*c.relpages "size",  age(relfrozenxid) as "icedXID",  autovacuum_freeze_table_age "AV TblIceAge",  CASE    WHEN age(relfrozenxid) > autovacuum_freeze_table_age      THEN ''AV''    WHEN age(relfrozenxid) > current_setting(''vacuum_freeze_table_age'')::integer      THEN ''V''  END AS "Icing",  autovacuum_freeze_max_age "IceMaxAV",  autovacuum_freeze_max_age - age(relfrozenxid) AS "ice_in",  ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || ''%'' AS "IceAV%"FROM  pg_stat_user_tables psut  JOIN pg_class c ON psut.relid = c.oid  JOIN storage_settings ON c.oid = storage_settings.oid  JOIN pg_tablespace spc on c.reltablespace=spc.oid or (c.reltablespace=0 and spc.spcname=''pg_default'')  /*LEFT JOIN pg_locks ON c.oid=pg_locks.relation WHERE pg_locks.relation is null*/ORDER BY ice_in)select "rel", spcname, "Pages", "Size", "icedXID", ice_in, "IceAV%" from _'

\set wrapstats '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/WITH table_opts AS (    SELECT      pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts    FROM      pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), storage_settings AS (    SELECT      oid, relname, nspname,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_threshold%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''autovacuum_vacuum_threshold'')::integer      END AS autovacuum_vacuum_threshold,      CASE        WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%''          THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real      END AS autovacuum_vacuum_scale_factor,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_min_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer          ELSE current_setting(''vacuum_freeze_min_age'')::integer      END AS autovacuum_freeze_min_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_table_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''vacuum_freeze_table_age'')::real      END AS autovacuum_freeze_table_age,      CASE        WHEN relopts LIKE ''%autovacuum_freeze_max_age%''          THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real          ELSE current_setting(''autovacuum_freeze_max_age'')::real      END AS autovacuum_freeze_max_age    FROM      table_opts),_ as (SELECT  storage_settings.nspname AS "Schema",  storage_settings.relname AS "Table",  to_char(pg_class.reltuples, ''9G999G999G999'') AS "Rows",  to_char(pg_class.relpages, ''9G999G999'') AS "Pages",  pg_size_pretty(pg_table_size(pg_class.oid)) AS "Size",  age(relfrozenxid) as "icedXID",  autovacuum_freeze_table_age "AV TblIceAge",  CASE    WHEN age(relfrozenxid) > autovacuum_freeze_table_age      THEN ''AV''    WHEN age(relfrozenxid) > current_setting(''vacuum_freeze_table_age'')::integer      THEN ''V''  END AS "Icing",  autovacuum_freeze_max_age "IceMaxAV",  autovacuum_freeze_max_age - age(relfrozenxid) AS "ice_in",  ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || ''%'' AS "IceAV%"FROM  pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid  INNER JOIN storage_settings ON pg_class.oid = storage_settings.oidORDER BY ice_in)select "Schema", "Table", "Pages", "Size", "icedXID", ice_in, "IceAV%" from _'



-- number of connected backends
\set backends 'SELECT datname, numbackends FROM pg_catalog.pg_stat_database;'

\set conninfo 'select usename, count(*) from pg_stat_activity group by usename;'

\set activity 'select datname, pid, usename, application_name,client_addr, client_hostname, client_port, state, left(query,20) query1 from pg_stat_activity;'

\set activity1 'WITH _0 AS (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age,          (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, state, left(query,20) query1 FROM pg_stat_activity order by xact_start desc nulls last, query_start desc) SELECT * FROM _0 ;'

\set activity96 'WITH _0 AS (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age,          (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, concat(STATE, CASE WHEN waiting THEN ''[WAIT]'' ELSE '''' END) AS STATE, regexp_replace(query, ''[ ]+'', '' '', ''g'') query1   FROM pg_stat_activity order by xact_start desc nulls last, query_start desc)SELECT * FROM _0; '

\set activity2 'WITH _0 AS (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age,          (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, state, left(regexp_replace(query, ''[ ]+'', '' '', ''g''),20) query1   FROM pg_stat_activity order by xact_start desc nulls last, query_start desc) SELECT * FROM _0;'

--\set activitytho1 'WITH _0 AS  (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age,          (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, state, left(regexp_replace(query, ''[ ]+'', '' '', ''g''),20) query1 FROM pg_stat_activity  WHERE pid <> pg_backend_pid()    AND state <> ''idle''  ORDER BY xact_start DESC NULLS LAST, query_start DESC)SELECT * FROM _0 ;'

\set activitytho 'WITH _0 AS  (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age,          (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, state, left(query,20) query1,  FROM pg_stat_activity  WHERE pid <> pg_backend_pid()    AND state <> ''idle''  ORDER BY xact_start DESC NULLS LAST,           query_start DESC)SELECT * FROM _0 ;'



\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'

\set tablesize 'SELECT nspname || \'.\' || relname AS \"relation\", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY pg_relation_size(C.oid) DESC LIMIT 40;'

\set tablesize1 'WITH _0 AS  (SELECT (nspname || \'.\' || relname) AS rel,    pg_size_pretty(pg_relation_size(C.oid)) AS "size"    FROM pg_class C    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)    WHERE nspname NOT IN (\'pg_catalog\', \'information_schema\')   ORDER BY pg_relation_size(C.oid) DESC)SELECT * from _0 ;'


\set uselesscol 'SELECT nspname, relname, attname, typname, (stanullfrac*100)::int AS null_percent, case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end AS \"distinct\", case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end AS \"values\" FROM pg_class c JOIN pg_namespace ns ON (ns.oid=relnamespace) JOIN pg_attribute ON (c.oid=attrelid) JOIN pg_type t ON (t.oid=atttypid) JOIN pg_statistic ON (c.oid=starelid AND staattnum=attnum) WHERE nspname NOT LIKE E\'pg\\\\_%\' AND nspname != \'information_schema\' AND relkind=\'r\' AND NOT attisdropped AND attstattarget != 0 AND reltuples >= 100 AND stadistinct BETWEEN 0 AND 1 ORDER BY nspname, relname, attname;'



Mas info en el gi: https://github.com/DiegoDAF/pgScrips

A principios de mes se subastó el manuscrito de la novela The Sign of Four, de Arthur Conan Doyle, en la que reáparecen el detective Sherlock Holmes y su ayudante, el doctor Watson.
Lá celebre pareja había nacido en la novela anterior de Doyle, Estudio en escarlata (de la que se conserva tan solo una página del manuscrito). A pesar de los reclamos de varios admiradores, Arthur Conan Doyle no quería verse obligado a desarrollar una serie de relatos que tuviera a Holmes y Watson como protagonistas. Sin embargo, cambió de parecer después de una cena realizada a fines de 1889 en un elegante hotel de Londres. Allí, el editor del mensuario literario norteamericano Lippincott`s les pidió, a él y a Oscar Wilde, novelas cortas para editárselas en los Estados Unidos.
Respondiendo a tal pedido, Wilde escribió El retrato de Dorian Gray y Doyle The Sign of Four. La aparición, en 1890, de esta última novela fue el inicio de un éxito comercial de tal magnitud que a Conan Doyle ya no le fue fácil resistirse a narrar nuevas aventuras del detective Sherlock Holmes.
Además de bastante dinero, esas novelas le dieron al escritor escocés una fama de la que abjuró al final de su vida. Conan Doyle creía que el detective había opacado “lo mejor de su obra” y que por eso nunca lograría, como su admirado amigo Oscar Wilde, un lugar destacado en la historia literaria. En esto, le faltó el olfato de su infalible personaje.

CLARIN – jueves 19 de diciembre de 1996

Rational

When you create a user in postgresql, even with only select only permission, your user has a lot more privileges.

Solution

Connect with root user to the desired database you will be giving permissions for:
mydb=# psql -h 10.10.10.10 -U postgres mydb
Now lets create the user:
mydb=# create user readonly with encrypted password 'readonly';
Revoke everything so we can explicitly allow what we need:
mydb=# revoke all on schema public from readonly;

Grant only what you need.

First give connection privilege to the actual database:
mydb=# GRANT CONNECT ON DATABASE mydb TO readonly;

Then grant usage and select:
mydb=# GRANT USAGE,SELECT ON SCHEMA public TO readonly;
mydb=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
If you want to grant access to the new tables in the future automatically, you have to alter default:
mydb=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

Fin

Thats all!

Fuente: parask.me/blog/postgresql-readonly-user

– gmapbmap.img = Mapa base, que viene con el equipo.
– gmapprom.img = Mapa precargado de fabrica (EEUU, Canada)
– gmapsupp.img = Mapear V12.4
– gmapprom1.img = Mapear Nautico V2

gmapsupp.img (always the default name from mapsource)
gmapsup1.img (additional on SD card)
gmapprom.img (Garmin main memory)
1 x gmapsupp.img – memoria interna del dispositivo
1 x gmapsupp.img – externa / SD Card
1 x gmapsup2.img – externa / SD Card
1 x gmapsup3.img – externa / SD Card