Postgres Lectures for improve performance

References:
[1] https://revs.runtime-revolution.com/streaming-data-in-postgres-43c502a6732
https://revs.runtime-revolution.com/streaming-data-in-postgres-43c502a6732
[2] https://blog.timescale.com/scalable-postgresql-high-availability-read-scalability-streaming-replication-fb95023e2af/
[3] https://www.pipelinedb.com/
[4] https://www.citusdata.com/blog/2017/11/08/faster-bulk-loading-in-postgresql-with-copy/
[5] https://dzone.com/articles/postgresql-vs-hadoop

PostgreSQL 8.4 en CentOS

1) Deshabilitar repositorios

#vi /etc/yum.repos.d/CentOS-Base.repo

agregar la siguiente linea a [base] y [updates]
exclude=postgresql*

2) Añadir los repositorios de PostgreSQL 8.4
Descargar el rpm apropiado desde http://yum.pgsqlrpms.org/reporpms/repoview/pgdg-centos.html

#wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-2.noarch.rpm
#rpm -ivh pgdg-centos-8.4-2.noarch.rpm (este archivos es el que descargamos)

3) Instalar PostgreSQL

3.1 verifique version
#yum list postgresql*

#yum install postgresql postgresql-server

3.1 Error probable de dependencia a apr-util
#yum install apr-util
#yum install postgresql postgresql-server

4) Arrancar el servidor y habilitar conexiones remotas

#service postgresql initdb
#service postgresql start

5) Configurar Accesos en /var/lib/pgsql/data/
5.1 pg_hba.conf
host all all 0.0.0.0/0 trust   #no need password , use md5
5.2 postgresql.conf
listen_addresses='*'
port=5432
max_connections=100
superuser_reserved_connections=5

6) Reinicia servicio
#/etc/init.d/postgresql restart


Referencias
[0] Postgres 9.1 http://wiki.postgresql.org/wiki/YUM_Installation
[1] Auto Start/Stop http://www.michaelhinds.com/tech/linux/install-postgres.html
[2] http://www.ixavi.com/2010/01/instalar-postgresql-8-3-en-centos-desde-yum/

Postgres PSql commands

http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html

Postgres - Fechas

Tipos Fecha
timestamp
timestamp with time zone
date
time
time with time zone
interval

Variables de Fecha
CURRENT_DATE : Fecha Actual
CURRENT_TIME : Hora Actual
CURRENT_TIMESTAMP : Fecha y Hora Actual
LOCALTIME
LOCALTIMESTAMP

referencias:

[1] http://elreinodelterror.wordpress.com/2011/02/01/fechas-y-horas-en-postgres/
[2] http://saforas.wordpress.com/2009/11/12/postgresql-fechas-y-horas/
[3] http://postgresql.ru.net/manual/functions-formatting.html

Postgres Backup guide

if you use pg_dump ppp > xxx.tar, then you get error, use standard format for big databases.


references:

[1] http://sparcki.blogspot.com/2010/11/copias-de-seguridad-y-recuperacion-en.html

[2] http://www.rolandovera.com/2008/07/09/forma-correcta-de-sacar-un-respaldo-backup-en-postgresql/

Postgres::Drop connections

--List all of the open connections to a given database
select * from pg_stat_activity where datname='database';

--Drop all of the open connections to a given database:
select pg_terminate_backend(procpid) from pg_stat_activity where datname='database';
--More samples
select * from pg_stat_activity where date(backend_start)='2011-04-19';
select pg_terminate_backend(procpid) from pg_stat_activity where date(backend_start)='2011-04-19';

--One sample more specific
select * from pg_stat_activity where backend_start < '2011-05-04 10:00' and waiting=false order by backend_start desc
select pg_terminate_backend(procpid) from pg_stat_activity where backend_start < '2011-05-04 10:00' and waiting=false

select pg_terminate_backend(procpid) from pg_stat_activity where backend_start < '2011-08-05 07:00' and datname='' and waiting=false

References:
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE
http://www.postgresql.org/docs/8.4/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE

PostgreSql Date Format

If you need change date format into postgres database, need execute next commands

SET DATESTYLE TO Postgres,US;
or
SET DATESTYLE TO iso,dmy --dd/mm/yyyy

this setting apply to current session; for permanent change modify postgresq.conf, section Locale and Formatting:

datestyle = 'iso, mdy'

Now you need reestart database

#service postgresql restart


Reload config settings without restarting database
If you are making modifications to the file postgresql.conf (or similar), and you want to new settings to take effect without needing to restart the entire database, there are two ways to accomplish this.

Option 1: From the command-line shell
#su - postgres
$/usr/bin/pg_ctl reload

Option 2: Using SQL

SELECT pg_reload_conf();

Using either option will not interrupt any active queries or connections to the database.

References:
[1] SET http://www.commandprompt.com/ppbook/r28464
[2] http://heatware.net/databases/postgresql-reload-config-without-restarting/

Postgres Backup CentOS

#su postgres

$ pg_dump -Ft mydb > db.tar
$ pg_restore -d newdb db.tar


References:
[1]http://linux.die.net/man/1/pg_dump
[2]http://linux.die.net/man/1/pg_restore

Postgres Auditory

Trace connections
select * from pg_stat_activity;

select client_addr,count(*) from pg_stat_activity group by client_addr;

select datname,client_addr,count(*) from pg_stat_activity group by datname,client_addr;

Test times of count(*)

EXPLAIN ANALYZE SELECT COUNT(*) FROM remitos;

Obtain rows(tuples) info

select * from pg_class where relname = 'remitos' and relkind = 'r';
select * from pg_stat_all_tables where relname = 'remitos';

and look at the n_tup_ins, n_tup_del, ...


references:
http://www.postgresonline.com/journal/archives/134-Terminating-Annoying-Back-Ends.html

Postgres CentOS iptables

# iptables -F
# iptables -L
# service iptables save
# service iptables restart
# iptables -L

references:
[1] http://www.cyberciti.biz/faq/turn-on-turn-off-firewall-in-linux/

SQL Manager for PostgreSQL

SQL Manager 2007 for PostgreSQL 4.5.0.8
http://www.megaupload.com/?d=3D0YCHSD

FATAL: sorry, too many clients already

Edit postgresql.conf,then on next lines increment values

max_connections = 8
shared_buffers = 16

queries for diagnostic current sessions.

1) Select * from pg_stat_activity;
2) Select count(*) as cnt, usename, current_query FROM pg_stat_activity Group by usename,current_query Order by cnt DESC;

Postgres DEBUG

Errors and Messages

RAISE level 'format' [, expression [, ...]];

levels-> DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION(raises an error).

format-> string, % is replaced by the next optional argument's string representation.

usages:
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
--This example replace % with v_job_id
RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
--This example will abort the transaction with the given error message:


references:
[1] http://www.postgresql.org/docs/8.1/static/plpgsql-errors-and-messages.html