## page was renamed from InstallPostgreSQLCentOS

<<TableOfContents(2)>>

= Postgresql =
PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. 

It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). 

It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. 

It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

http://www.postgresql.org/about/

== Install Postgresql on CentOS 6.4 ==

These installation steps should also apply to other distros based on RedHat source RPMs.

Logged in as root on the system run the following commands:
{{{#!highlight sh
yum install postgresql
yum install postgresql-devel
yum install postgresql-server
service postgresql initdb
chkconfig postgresql on
service postgresql start
}}}

Edit the file ''' /var/lib/pgsql/data/pg_hba.conf  '''
{{{#!highlight sh
local all all ident
host all all 0.0.0.0/0 password
}}}
To restart the postgresl DB run the command: 
{{{#!highlight sh
service postgresql restart
}}}

== Check postgresql version ==
{{{#!highlight bash
cat /var/lib/pgsql/data/PG_VERSION
}}}

== Create postgresql test user and database ==
{{{#!highlight sh
su postgres
psql
}}}
{{{#!highlight sql
create user appuser with login password '12345678';
create database app;
grant all privileges on database app to appuser;
\q
}}}


== Configure postgresql to accept all TCP connections ==
Edit the file ''' /var/lib/pgsql/data/postgresql.conf'''
{{{
listen-address="*"
port = 5432
max_connections = 100
}}}
Restart again the postgresl DB: 
{{{#!highlight sh
service postgresql restart
}}}

== Test the connection using the user and password ==
{{{#!highlight sh
psql -U appuser -h 127.0.0.1 -W -d app
}}}

== Backup and restore with pg_dump ==
{{{#!highlight sh
su postgres
pg_dump databasex > /var/lib/pgsql/backDatabasex.sql
psql databasex < /var/lib/pgsql/backDatabasex.sql # restore
# pg_dumpall 
pg_dumpall -f dump.sql 
}}}

== Daily backup script ==
{{{#!highlight bash
#!/bin/sh
#backupDBs.sh
#chmod 755 backupDBs.sh 
#backup database postgresql
#crontab -e 
#@daily /var/lib/pgsql/backupDBs.sh
BACKUPFOLDER=/var/lib/pgsql/backups
CURRDATE=`date -u "+%Y-%m-%dT%H:%m:%S.%Z"`
FILE=$BACKUPFOLDER/backup$CURRDATE.sql
DATABASE=dbx
/usr/bin/pg_dump $DATABASE > $FILE
gzip $FILE
}}}

== Show table structure ==
{{{#!highlight sh
su postgres
}}}

{{{#!highlight sql
\c databasex
\dt
\d+ tablex
}}}

== Drop a table column ==
{{{#!highlight sh
su postgres
}}}
{{{#!highlight sql
\c databasex
alter table tablex drop column columnx;
}}}

== Epoch from date field ==
Number of seconds since 1st January 1970 UTC.

{{{#!highlight sql
SELECT field1, field2, field3, extract(epoch from begindate) as begindate,extract(epoch from enddate) as enddate 
FROM tableX 
}}}

== Garbage-collect and analyze a PostgreSQL database ==
Create file /var/lib/pgsql/vacuumdb.sh and chmod 755 it
{{{#!highlight bash
#!/bin/sh
/usr/bin/vacuumdb --all --analyze --verbose
}}}

Create cron job to user postgres, crontab -e
{{{
@daily /var/lib/pgsql/vacuumdb.sh
}}}

== Install in debian buster ==
{{{#!highlight sh
apt install postgresql-11
netstat -at -n | grep 5432
ss -a -n | grep 5432

# pg_hba.conf
host all all 0.0.0.0/0 password
# postgresql.conf
listen-address="*"

# change postgres pass
sudo bash
su - postgres 
psql 
alter user postgres with password '<define pass here>';
}}}

== Give super user rights to user ==
{{{#!highlight sql
ALTER USER appuser WITH SUPERUSER;
ALTER USER appuser WITH NOSUPERUSER;
}}}

== List databases ==
{{{#!highlight sh
su postgres
psql
postgres=# \l
}}}

== Run in docker container ==
{{{#!highlight sh
docker network create mynet
docker run -p 5432:5432 --rm --name postgres-server -e POSTGRES_PASSWORD=postgres --network mynet -d postgres:15.3-alpine
docker exec -it postgres-server sh
psql -U postgres
\l
\q
exit
}}}

== Create schema ==

A schema organizes database objects into logical groups to make them more manageable.
 * https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS
{{{#!highlight sql
create schema if not exists my_schema;
}}}

== Insert record with sequence value and current date ==
{{{#!highlight sql
insert into tablex (id, curdate)
values( nextval('seqx'), current_date );
}}}

== Search path ==
{{{#!highlight sql
-- Show current search path
SHOW search_path;
-- Set search path
SET search_path TO myschema,public;
}}}