PostgreSQL User Guide

The CentOS series installation is divided into:

1. Yum Installation

2. SOURCE Installation

One, yum installation

Follow the official installation documentation.

1. Install PostgreSQL official Yum repository

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

2. Installing the PostgreSQL database

   Yum Install Postgresql96-server Postgresql96-contrib

3. Initializing and starting the database

   Service PostgreSQL Initdb

   Service PostgreSQL Start

4. Test data

   Su-postgres

   Psql-l

5. 
   

Second, the source code installation

./configure
make
su
make install
adduser postgres
mkdir / usr / local / pgsql / data
chown postgres / usr / local / pgsql / data
su-postgres
/ usr / local / pgsql / bin / initdb -D / usr / local / pgsql / data #Initialize the database
/ usr / local / pgsql / bin / postgres -D / usr / local / pgsql / data> logfile 2> & 1 & #Run postgresql database as daemon
/ usr / local / pgsql / bin / createdb test #Create a test postgresql database
/ usr / local / pgsql / bin / psql test #View the test database 

Set PostgreSQL directory to system environment variables

Add User Postgres

Copy the system service startup script to the appropriate directory of the system

CP Contrib/start-scripts/linux/etc/init.d/postgresql

Configure PostgreSQL database remote Access regardless of how the database is installed.

Modify the pg_hba.conf file, configure the new IP address, and netmask access the database.

Modify the listen_addresses= "localhost" configuration entry for the postgresql.conf file, and localhost is replaced with * to listen for all IP addresses.

Then restart the PostgreSQL service to take effect.

Basic operation of PostgreSQL:

After installation, a database named Postgres and a database user named Postgres are generated by default. It is important to note that a Linux system user named Postgres is also generated.

The first method, use the PostgreSQL console.

Switch to the Postgres user.

sudo su-postgres

Log in to the PostgreSQL console using the Psql command.

The equivalent of a system user Postgres to the database as a database user with the same name, without entering a password. If all goes well, the system prompt changes to "postgres=#", which means that the database console is now in. The following commands are completed within the console.

The first thing is to use the \password command to set a password for the Postgres user.

\password Postgres

The second thing is to create the database user Dbuser (a Linux system user just created) and set the password.

CREATE USER dbuser with PASSWORD ' PASSWORD ';

The third thing is to create the user database, here exampledb, and specify the owner as Dbuser.

CREATE DATABASE exampledb OWNER dbuser;

The fourth thing is to assign all the permissions of the Exampledb database to Dbuser, otherwise dbuser can only log in to the console without any database operation permissions.

GRANT all privileges on the DATABASE exampledb to Dbuser;

Finally, use the \q command to exit the console (you can also press ctrl+d directly).

\q

The second method, use the shell command line.

Adding new users and new databases can be done under the shell command line, in addition to the PostgreSQL console. This is because PostgreSQL provides command-line programs CreateUser and Createdb. Take the new user Dbuser and database Exampledb as an example.

First, create a database user, Dbuser, and specify it as a super user.

Sudo-u postgres CreateUser--superuser dbuser

Then, log in to the database console, set the password for the Dbuser user, and exit the console when you are finished.

Sudo-u Postgres Psql

\password Dbuser

\q

Next, under the shell command line, create the database exampledb and specify the owner as Dbuser.

Sudo-u postgres createdb-o dbuser exampledb

Log in to Database

After adding a new user and a new database, the database will be logged in the name of the new user, using the psql command.

Psql-u dbuser-d exampledb-h 127.0.0.1-p 5432

The parameters of the above command have the following meanings:-u Specify the user,-D specify the database,-h specifies the server,-p specifies the port.

After entering the above command, you will be prompted to enter the password for the Dbuser user. Enter it correctly and you can log in to the console.

The Psql command has a shorthand form. If the current Linux system user is also a PostgreSQL user, you can omit the user name (the part of the-u parameter). For example, my Linux system user name is RUANYF, and the PostgreSQL database has the same name as the user, then I log on to the Linux system as RUANYF, you can directly use the following command to log into the database, and do not require a password.

Psql Exampledb

If a database with the same name as the current system user exists inside PostgreSQL, then the database names can be omitted. For example, if there is a database called RUANYF, you can log in to the database by typing psql directly.

Psql

In addition, if you want to recover external data, you can use the following command.

Psql Exampledb < Exampledb.sql

Console commands

In addition to the \password command (set password) and the \q command (exit) that were previously used, the console provides a series of other commands.

\h: View an explanation of the SQL command, such as \h Select.

\?: View the list of PSQL commands.

\l: Lists all databases.

\c [database_name]: Connect to a different database.

\d: Lists all tables for the current database.

\d [table_name]: Lists the structure of a table.

\DU: Lists all users.

\e: Opens a text editor.

\conninfo: Lists the current database and connection information.

PostgreSQL User Guide