Shulou(Shulou.com)05/31 Report--

This article mainly introduces the CentOS/RHEL 7 on how to install PostgreSQL configuration, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Install PostgreSQL 9.6 as an 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, and listen for 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 identhost all all 32 md5

Restart PostgreSQL

Systemctl restart postgresql-9.6

Authentication mode

Authentication methods support "trust", "reject", "md5", "password", "gss", "sspi", "ident", "peer", "pam", "ldap", "radius", "cert".

Trust anyone can access the database and you need to specify a database user name. As above, you can connect to the database locally using psql-U postgres (default is root when no database user name is specified).

Password password authentication, send clear text password

Md5 password authentication, send the password encrypted by MD5. If the database server IP is 10.188.13.29, you can access it like this: psql-h 10.188.13.29-U postgres. Enter will be prompted for the password.

Ident gets the user name of the client operating system from ident server and can be accessed when it matches the database user name. When ident is configured on a local connection, peer is used instead. There are security risks, only applicable to closed networks, not recommended.

Peer gets the user name of the client operating system from kernel, which can be accessed when it matches the database user name, and is used only for local connections. If local is configured as peer, you can access psql-U postgres like this

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

Then configure to use map in pg_hba.conf:

Local all all peer map=omicronhost all all 127.0.0.1/32 ident map=omicronPSQL

Connect PostgreSQL

Psql-U postgres

For more parameters, please see help psql-help.

Refresh configuration

After modifying the configuration file, you can execute the following command to refresh the configuration:

Select pg_reload_conf ()

Change password

ALTER USER postgres WITH PASSWORD 'postgres'

View users

Select * from pg_shadow

View the directory where the data folder is located

Show data_directory

Create a 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 up Search Path

SET search_path TO test

View Table

\ dt

View Sequence

\ ds

View View

\ dv

View Table Sequence View

\ d

Execute sql script

\ I test.sql

Sequence

Query sequence (currval (), nextval ())

Select nextval ('test_sequence')

Update sequence

Alter sequence test_sequence restart with 42

Quit

\ Q

Help

Help

\? For help with psql commands

\ h for help with SQL commands

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 procedure

A small stored procedure that clears 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 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$ Thank you for reading this article carefully. I hope the article "how to install and configure PostgreSQL on CentOS/RHEL 7" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.