Installation configuration and basic use of Centos/rhel 7 on PostgreSQL

Install PostgreSQL 9.6 For example:?

Installation

Install the repository RPM
Yum Install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm

Install the client Packages
Yum Install postgresql96

Install the server packages
Yum Install Postgresql96-server

Initialize the database and enable automatic start
/usr/pgsql-9.6/bin/postgresql96-setup Initdb
Systemctl Enable postgresql-9.6
Systemctl start postgresql-9.6?

Configuration

Edit/var/lib/pgsql/9.6/data/postgresql.conf, modify Listen_addresses, listen to all addresses:

listen_addresses = ‘*‘

Edit/var/lib/pgsql/9.6/data/pg_hba.conf, modify the authentication method:

# "local" is for Unix domain socket connections onlylocal?? all???????????? all????????????????????????????????????         trust# IPv4 local connections:host??? all???????????? all???????????? 127.0.0.1/32??????????? identhost??? all???????????? all???????????? 0.0.0.0/0????????????????? md5

Restart PostgreSQL
Systemctl Restart postgresql-9.6?

Authentication method

Authentication Methods Support "Trust", "Reject", "MD5", "Password", "GSS", "SSPI", "Ident", "peer", "Pam", "LDAP", "radius", "cert".

• Trust Anyone can access the database and need to specify the database user name. As above, the database can be connected locally using Psql-u Postgres (default is root when no database user name is specified).
• Password password authentication, send plaintext password
• MD5? password authentication, send MD5 encrypted password, if the database server IP is 10.188.13.29, then you can access: Psql-h 10.188.13.29- U Postgres, you will be prompted to enter the password after the return.
• ident? Gets the user name of the client operating system from Ident server, which is accessible when it matches the database user name. When ident is configured on a local connection, a peer substitution is used. There is a security risk that applies only to closed networks and is not recommended for use.
• Peer Gets the user name of the client operating system from kernel, which is accessible when matching the database user name, and is used only for local connections. If local is configured as peer, you can access psql-u Postgres
  When the operating system user name is inconsistent with the database user name, you can configure the map relationship in the file pg_ident.conf as follows:

   # MAPNAME?????? System-username????????   Pg-usernameomicron????????????? Root??  ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??? Postgres

  And then configure the use map in pg_hba.conf:

   local? All???????????? all???????????????????????????????? ??? ? ? ? ?? Peer Map=omicronhost??? All???????????? All???????????? 127.0.0.1/32??????????? Ident Map=omicron

  PSQL

  connection PostgreSQL
  psql-u postgres

More parameters to view Help Psql--help
?
Refresh Configuration
After you modify the configuration file, you can perform the following commands to refresh the configuration:
Select pg_reload_conf ();
?
Change Password
ALTER USER postgres with PASSWORD ' postgres ';
?
View User
SELECT * from Pg_shadow;
?
View the directory where the data folder is located
Show Data_directory;
?
Create user
CREATE USER test with PASSWORD ' test ';
ALTER USER test with SUPERUSER;
?
Create schema
CREATE SCHEMA test;
ALTER SCHEMA test OWNER to test;
?
View Schema
\dn
?
Set Search Path
SET Search_path to test;
?
Execute SQL Script
\i Test.sql
?
Sequence
Query sequence (Currval (), Nextval ())
Select Nextval (' test_sequence ');

Update sequence
Alter sequence test_sequence restart with 42;
?
Exit
\q
?
Help
Help
\?
\h
?

Backup and Recovery

Pg_dump-h host1?-u Postgres [-N schema]?dbname > outfile
Psql-u Postgres dbname < infile
?
You can also back up the data directory directly
TAR-CF Backup.tar/usr/local/pgsql/data

Stored Procedures

A small stored procedure that empties all table data (schema name is test):

--?FUNCTION:?test.truncatealltable()??
??
--?DROP?FUNCTION?test.truncatealltable();??
??
CREATE?OR?REPLACE?FUNCTION?test.truncatealltable()??
????RETURNS?text??
????LANGUAGE?‘plpgsql‘??
??
AS?$BODY$??
??
DECLARE??
????cur_all_tables?CURSOR?FOR??
??????select?relname?from?pg_class??
??????where?relnamespace?=?(select?oid?from?pg_namespace?where?nspname?=?‘test‘)??
????????and?relkind?=?‘r‘?order?by?relname;??
????truncate_sql?CHARACTER?VARYING(100);??
?????
BEGIN??????
????FOR?record?IN?cur_all_tables??
????LOOP?????????????
????????truncate_sql?:=?concat(‘truncate?table?test.‘,?record.relname,?‘?cascade‘);??
????????EXECUTE?truncate_sql;??????????
????END?LOOP;??
??
????return?‘success‘;??
END??
??
$BODY$;??

PostgreSQL

Installation configuration and basic use of PostgreSQL on Centos/rhel 7