by ·Comments Off on Primary Key Index is a Space Hog

Tables in PostgreSQL have an index for the primary key and separate row storage called the heap. Other databases integrate these together or support “index-organized tables”. In this arrangement, the primary key lookup process leads directly to the row data without a secondary fetch to get the full row and the requisite additional CPU and I/O utilization.

The CLUSTER command in PostgreSQL reorganizes a table according to an index to improve performance, but doesn’t really work for most real-world OLTP cases. It rewrites the entire table under an exclusive lock, blocking any reads or writes. PostgreSQL doesn’t maintain the clustered layout for new data, so this operation must be ran periodically. So it is really only useful if you can take your database offline for long periods of time on a regular basis.

But more critically, index-organized tables save space as the index doesn’t require a separate copy of the row data. For tables with small rows that are mostly covered by the primary key, such as join tables, this can easily cut the table’s storage footprint in half.

Consider the following table which stores social “likes” for arbitrary objects:

CREATE TABLE likes (
 object_type INTEGER NOT NULL,
 object_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
 user_id BIGINT NOT NULL,
 created_at TIMESTAMP WITH TIME ZONE NOT NULL,
 PRIMARY KEY(object_type, object_id, user_id)
);

PostgreSQL will maintain an index for the primary key which is separate from the base table storage. This index will contain a full copy of the object_type, object_id, and user_id columns for every row. 20 out of 28 bytes in each row (~70%) will be duplicated. If PostgreSQL supported index-organized tables, it wouldn’t consume all that additional space.

Fuente: medium.com/@rbranson/10-things-i-hate-about-postgresql-20dbab8c2791

by ·Comments Off on Monitor PostgreSQL using Zabbix

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.

Zabbix Related Tutorial:

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

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/

by ·Comments Off on How To Install PostgreSQL With Docker Quickly

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

by ·Comments Off on Mi .psqlrc

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

by ·Comments Off on Foreign data wrappers en Postgres

Quien no se encontro alguna vez con la necesidad de traer algun dato desde un SQL Server a un Postgres…???

Bien, tenia esto:

Un postgres 9.6.5 y un SQL Server 2014 SP2 (12.0.5207.0) todo en 64bits. Y supongamos que tambien tienen un usaurio y clave del SQL Server, asi como el nombre de la tabla, en mi caso daf.

Enconces, empece:

En el Centos con Root (esto es la instalacion, no lo hagan si ya lo tienen):

    • yum install postgresql96-server
    • yum install freetds freetds-devel
    • yum install postgresql96-libs postgresql96-devel
    • firewall-cmd –permanent –add-port=5432/tcp
    • firewall-cmd –reload

En el Centos con Postgers (esto es la instalacion, no lo hagan si ya lo tienen):

    • /usr/pgsql-9.6/bin/initdb -E=UTF-8 -U postgres -A trust -W -D /bases_postgres/data
    • vi postgresql.conf
    • vi pg_hba.conf
    • /usr/pgsql-9.6/bin/pg_ctl -D /bases_postgres/data start

En el Centos con Root:

    • export TDS_FDW_VERSION=”1.0.8″
    • wget https://github.com/tds-fdw/tds_fdw/archive/v${TDS_FDW_VERSION}.tar.gz -O tds_fdw-${TDS_FDW_VERSION}.tar.gz
    • tar -xvzf tds_fdw-${TDS_FDW_VERSION}.tar.gz
    • cd tds_fdw-${TDS_FDW_VERSION}
    • PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1
    • PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1 install
    • ping sqlserver
    • telnet sqlserver 1433
    • tsql -S sqlserver -U fdw

En el PGADMIN con Postgres:

    • CREATE EXTENSION tds_fdw;
    • create server tds_testserver foreign data wrapper tds_fdw options (servername ‘sqlserver’, port ‘1433’, database ‘diego_test_fdw’, tds_version ‘4.2’, character_set ‘UTF-8’);
    • create user mapping for postgres server tds_testserver options (username ‘fdw’, password ‘daf’);
    • create foreign table daf (id integer, valor varchar(100) ) server tds_testserver options (table ‘daf’);
    • select * from daf;

Estos pasos se siguieron basados en la union de estas notas:
Noten que hay varios cambios

by ·2 Comments

El pgAdmin III, tiene sucesor, un completamente nuevo pgAdmin 4 que por desgracia esta muuuuuuuuy verde.
Lo pueden encontrar en su sitio: pgadmin.org

Pros:

  • Soporta v9.6
  • Una vez conectado el arbol carga rapido.

Contras:

  • No tiene buscar (ctrl+F), reemplazar, nuevo (ctrl+N; (ctrl+E))
  • No podes importar los servidores registrados en el PGAdmin 3
  • No podes cambiar de solapa con ctrl+tab
  • Tarda en arrancar

by ·Comments Off on Datazo V

SELECT unnest(string_to_array(‘1,2,3,4’, ‘,’))

un string lo transformas a un array y el unnest te lo pone en filitas… bien tecnico

Saludos,

by ·Comments Off on Como ver el estado del servidor postgressql sin ser superuser
CREATE FUNCTION get_sa() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE VIEW pg_stat_activity_allusers AS SELECT * FROM get_sa();

GRANT SELECT ON pg_stat_activity_allusers TO public;

select * from pg_stat_activity_allusers;

by ·Comments Off on DATAZO III – Funciones de string en Postgres, conocidas y desconocidas
n buen resumen de muchisimas funciones de strings, conocidas y desconocidas:
Todo en un PDF:
Por ejemplo, cómo parsear un string separado por comas:
SPLIT_PART ('1,2,3', ',', 2) = '2'
cómo rellenar con ceros:
LPAD ('123', 6, '0') = '000123'
cómo escribir con iniciales mayusculas:
INITCAP ('hi thomas') = 'Hi Thomas'
etc, etc etc.