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:
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".
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