Differences between revisions 4 and 34 (spanning 30 versions)

Contents

Postgresql

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:

   1 yum install postgresql
   2 yum install postgresql-devel
   3 yum install postgresql-server
   4 service postgresql initdb
   5 chkconfig postgresql on
   6 service postgresql start

Edit the file /var/lib/pgsql/data/pg_hba.conf

   1 local all all ident
   2 host all all 0.0.0.0/0 password

To restart the postgresl DB run the command:

   1 service postgresql restart

Check postgresql version

   1 cat /var/lib/pgsql/data/PG_VERSION

Create postgresql test user and database

   1 su postgres
   2 psql

   1 create user appuser with login password '12345678';
   2 create database app;
   3 grant all privileges on database app to appuser;
   4 \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:

   1 service postgresql restart

Test the connection using the user and password

   1 psql -U appuser -h 127.0.0.1 -W -d app

Backup and restore with pg_dump

   1 su postgres
   2 pg_dump databasex > /var/lib/pgsql/backDatabasex.sql
   3 psql databasex < /var/lib/pgsql/backDatabasex.sql # restore
   4 # pg_dumpall 
   5 pg_dumpall -f dump.sql

Daily backup script

   1 #!/bin/sh
   2 #backupDBs.sh
   3 #chmod 755 backupDBs.sh 
   4 #backup database postgresql
   5 #crontab -e 
   6 #@daily /var/lib/pgsql/backupDBs.sh
   7 BACKUPFOLDER=/var/lib/pgsql/backups
   8 CURRDATE=`date -u "+%Y-%m-%dT%H:%m:%S.%Z"`
   9 FILE=$BACKUPFOLDER/backup$CURRDATE.sql
  10 DATABASE=dbx
  11 /usr/bin/pg_dump $DATABASE > $FILE
  12 gzip $FILE

Show table structure

   1 su postgres

   1 \c databasex
   2 \dt
   3 \d+ tablex

Drop a table column

   1 su postgres

   1 \c databasex
   2 alter table tablex drop column columnx;

Epoch from date field

Number of seconds since 1st January 1970 UTC.

   1 SELECT field1, field2, field3, extract(epoch from begindate) as begindate,extract(epoch from enddate) as enddate 
   2 FROM tableX

Garbage-collect and analyze a PostgreSQL database

Create file /var/lib/pgsql/vacuumdb.sh and chmod 755 it

   1 #!/bin/sh
   2 /usr/bin/vacuumdb --all --analyze --verbose

Create cron job to user postgres, crontab -e

@daily /var/lib/pgsql/vacuumdb.sh

Install in debian buster

   1 apt install postgresql-11
   2 netstat -at -n | grep 5432
   3 ss -a -n | grep 5432
   4 
   5 # pg_hba.conf
   6 host all all 0.0.0.0/0 password
   7 # postgresql.conf
   8 listen-address="*"
   9 
  10 # change postgres pass
  11 sudo bash
  12 su - postgres 
  13 psql 
  14 alter user postgres with password '<define pass here>';

Give super user rights to user

   1 ALTER USER appuser WITH SUPERUSER;
   2 ALTER USER appuser WITH NOSUPERUSER;

List databases

   1 su postgres
   2 psql
   3 postgres=# \l
   4

Run postgres-15 in docker container

   1 docker network create mynet
   2 docker run -p 5432:5432 --rm --name postgres-server -e POSTGRES_PASSWORD=postgres --network mynet -d postgres:15.3-alpine
   3 docker exec -it postgres-server sh
   4 psql -U postgres
   5 \l
   6 \q
   7 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

   1 create schema if not exists my_schema;

Insert record with sequence value and current date

   1 insert into tablex (id, curdate)
   2 values( nextval('seqx'), current_date );

Search path

   1 -- Show current search path
   2 SHOW search_path;
   3 -- Set search path
   4 SET search_path TO myschema,public;

Install postgresql 15 in debian 12 bookworm

   1 sudo apt install postgresql-15
   2 ss -a -n | grep 5432

/etc/postgresql/15/main/pg_hba.conf

   1 cat /etc/postgresql/15/main/pg_hba.conf | grep -v "#"
   2 local   all             all                                     peer
   3 host    all             all             127.0.0.1/32            scram-sha-256
   4 host    all             all             ::1/128                 scram-sha-256
   5 host    all             all             0.0.0.0/0                password

/etc/postgresql/15/main/postgresql.conf

   1 data_directory = '/var/lib/postgresql/15/main'
   2 hba_file = '/etc/postgresql/15/main/pg_hba.conf'        
   3 ident_file = '/etc/postgresql/15/main/pg_ident.conf'    
   4 external_pid_file = '/var/run/postgresql/15-main.pid'   
   5 unix_socket_directories = '/var/run/postgresql'
   6 shared_buffers = 128MB                  
   7 dynamic_shared_memory_type = posix      
   8 max_wal_size = 1GB
   9 min_wal_size = 80MB
  10 log_line_prefix = '%m [%p] %q%u@%d '            
  11 log_timezone = 'Europe/Lisbon'
  12 cluster_name = '15/main'                        
  13 datestyle = 'iso, mdy'
  14 timezone = 'Europe/Lisbon'      
  15 default_text_search_config = 'pg_catalog.english'
  16 include_dir = 'conf.d'                  
  17 listen_addresses = '*'

check connections

   1 sudo service postgresql restart
   2 sudo service postgresql status
   3 pg_isready 
   4 # /var/run/postgresql:5432 - accepting connections
   5

Create DB and schema

   1 sudo bash 
   2 su postgres 
   3 psql
   4 create user appuser with login password '12345678';
   5 create database app;
   6 grant all privileges on database app to appuser;
   7 create schema if not exists myschema;
   8 \q

Create table and sequence

   1 psql -U appuser -h 127.0.0.1 -W -d app
   2 psql -U appuser -h localhost -W -d app
   3 
   4 create schema if not exists myschema;
   5 SET search_path TO myschema;
   6 create sequence if not exists device_seq; 
   7 
   8 CREATE TABLE devices ( 
   9   id        integer PRIMARY KEY DEFAULT nextval('device_seq'),
  10   device      varchar(128),
  11   apikey      varchar(128)
  12 );
  13 
  14 insert into devices(device,apikey) values('device','api');
  15 select * from devices;

-  ⇤ ← Revision 4 as of 2013-08-13 15:07:40 → 
  Size: 1769
  Editor: bl12-209-207
  Comment:
+   ← Revision 34 as of 2025-03-07 16:03:30 → ⇥
  Size: 7343
  Editor: vitor
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 1:
-= Install Postgresql on CentOS 6.4 =
+## 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 ==
-Line 6:
+Line 21:
- * yum install postgresql
 * yum install postgresql-devel
 * yum install postgresql-server
 * service postgresql initdb
 * chkconfig postgresql on
 * service postgresql start
+{{{#!highlight sh
yum install postgresql
yum install postgresql-devel
yum install postgresql-server
service postgresql initdb
chkconfig postgresql on
service postgresql start
}}}
-Line 14:
+Line 31:
- * local all all ident
 * host all all 0.0.0.0/0 password
+{{{#!highlight sh
local all all ident
host all all 0.0.0.0/0 password
}}}
-Line 18:
+Line 36:
- * service postgresql restart
+{{{#!highlight sh
service postgresql restart
}}}

== Check postgresql version ==
{{{#!highlight bash
cat /var/lib/pgsql/data/PG_VERSION
}}}
-Line 21:
+Line 46:
- * su postgres
 * psql
 * create user appuser with login password '12345678';
 * create database app;
 * grant all privileges on database app to appuser;
 * \q
+{{{#!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
}}}
-Line 29:
+Line 59:
-Edit the file ''' /var/lib/pgsql/data(postgresql.conf '''
 * listen-address="*"
 * port = 5432
 * max_connections = 100
+Edit the file ''' /var/lib/pgsql/data/postgresql.conf'''
{{{
listen-address="*"
port = 5432
max_connections = 100
}}}
-Line 35:
+Line 66:
- * service postgresql restart
+{{{#!highlight sh
service postgresql restart
}}}
-Line 38:
+Line 71:
-psql -U appuser -h 127.0.0.1 -W -d a++
+{{{#!highlight sh
psql -U appuser -h 127.0.0.1 -W -d app
}}}
-Line 41:
+Line 76:
- * su postgres
 * pg_dump databasex > /var/lib/pgsql/backDatabasex.sql
 * psql databasex < /var/lib/pgsql/backDatabasex.sql # restore

== Daily backup script==
+{{{#!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 ==
-Line 62:
+Line 101:
- * su postgres
 * \c databasex
 * \dt
 * \d+ tablex
+{{{#!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 postgres-15 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;
}}}

== Install postgresql 15 in debian 12 bookworm ==
{{{#!highlight sh
sudo apt install postgresql-15
ss -a -n | grep 5432
}}}

=== /etc/postgresql/15/main/pg_hba.conf ===
{{{#!highlight sh
cat /etc/postgresql/15/main/pg_hba.conf | grep -v "#"
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0                password
}}}

=== /etc/postgresql/15/main/postgresql.conf ===
{{{#!highlight sh
data_directory = '/var/lib/postgresql/15/main'
hba_file = '/etc/postgresql/15/main/pg_hba.conf'	
ident_file = '/etc/postgresql/15/main/pg_ident.conf'	
external_pid_file = '/var/run/postgresql/15-main.pid'	
unix_socket_directories = '/var/run/postgresql'
shared_buffers = 128MB			
dynamic_shared_memory_type = posix	
max_wal_size = 1GB
min_wal_size = 80MB
log_line_prefix = '%m [%p] %q%u@%d '		
log_timezone = 'Europe/Lisbon'
cluster_name = '15/main'			
datestyle = 'iso, mdy'
timezone = 'Europe/Lisbon'	
default_text_search_config = 'pg_catalog.english'
include_dir = 'conf.d'			
listen_addresses = '*'
}}}

=== check connections ===
{{{#!highlight sh
sudo service postgresql restart
sudo service postgresql status
pg_isready 
# /var/run/postgresql:5432 - accepting connections
}}}

=== Create DB and schema ===
{{{#!highlight sh
sudo bash 
su postgres 
psql
create user appuser with login password '12345678';
create database app;
grant all privileges on database app to appuser;
create schema if not exists myschema;
\q
}}}

=== Create table and sequence ===
{{{#!highlight sh
psql -U appuser -h 127.0.0.1 -W -d app
psql -U appuser -h localhost -W -d app

create schema if not exists myschema;
SET search_path TO myschema;
create sequence if not exists device_seq; 

CREATE TABLE devices ( 
  id        integer PRIMARY KEY DEFAULT nextval('device_seq'),
  device      varchar(128),
  apikey      varchar(128)
);

insert into devices(device,apikey) values('device','api');
select * from devices;
}}}