## page was renamed from InstallPostgreSQLCentOS <> = 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 ''; }}} == 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; }}}