Postgresql 11 installation step by step. Postgresql Learning Series

Postgresql 11 installation on Linux/UNIX

If you want to install postgresql 11  v11, it is as follows:

Step#1, installation

1. Create user

groupadd postgres
useradd -g postgres postgres

mkdir /u01/data/postgres

2. Download and install

cd /u01/data/tools/

wget https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.gz

tar xf postgresql-11.0.tar.gz
cd postgresql-11.0 /

 ./configure --prefix=/u01/data/postgres --without-readline


Note: If you do not specify an installation directory, it will be installed under /usr/local/ by default.

If the compilation fails, install the corresponding package according to the prompt message yum. You can continue without error reporting. You can also see the help information related to a compilation by make/

make --help. The make make process is time-consuming.

Print the following information.

All of PostgreSQL successfully made. Ready to install. After

make install is successful, it will print as follows Information:

OK,now PostgreSQL installation is complete

3. Check the directory structure

cd /u01/data/postgres/

 [root@pankajpc postgres]# ll

 total usage 16

 drwxr-xr-x 2 root root 4096 June 01 15:59 bin
 drwxr-xr-x 6 root root 4096 June 01 15:59 include
 drwxr-xr-x 4 root root 4096 June 01 15:59 lib

 drwxr-xr-x 6 root root 4096 June 01 15:59 share

Configure environment variables and directory permissions

vi  /etc/profile

export PGHOME=/u01/data/postgres

export PGDATA=/u01/data/postgres/data

export PGLIB=/u01/data/postgres/lib

export PATH=$PGHOME/bin:$PATH

source /etc/profile

mkdir /u01/data/postgres/data

mkdir /u01/data/postgres/logs

chown -R postgres:postgres ../postgres

5. Switch the user to initialize the database, and then start the database

su - postgres
initdb -D /u01/data/postgres/data/

You can print the following information to Success. You can now start the database server using:

pg_ctl -D /u01/data/postgres/data/ -l logfile start

Start the database

pg_ctl -D /u01/data/postgres/data/ -l /u01/data/postgres/logs/logfile start

waiting for server to start.... done

 server started

 successfully started

You can view the startup log cat logs/logfile at this time you can view the data directory, the corresponding configuration file and directory have been generated.

Step#2, the configuration

1. Change user password

There is no password after initialization, you can directly connect

su postgres or psql -U postgres

 psql

psql (11.0)

 Type "help" for help.

postgres=#

Change password

 postgres=# alter user postgres with password'postgres@123'

 postgres-#

\q logout

 login

psql -U postgres -W with username and password

• 2. Modify the default configuration file
• cd /u01/data/postgres/data
• after initialization is basically the default configuration file, you can modify
• vim postgresql.conf

In the configuration file, only postgresql can be accessed locally by default;

modify listen_addresses ='localhost' to listen_addresses ='*' to allow all remote access;

modifying the configuration file requires restarting the service.

Default listening address

! #listen_addresses ='localhost'

listen_addresses = ' ' !#Default

listening port

!#port = 5432 !#Maximum

number of connections, default 100

max_connections = 100

.Others can refer to the Chinese community

3. Host authentication

Modifying the pg_hba.conf file requires restarting the service

vi pg_hba.conf

!# After line 82, add the allowed clients under "IPv4 local connections";

!# "host" represents the host type, the first " "all" stands for db, the second "all" stands for user, "172.29.3.67/32" stands for client ip, and "trust" stands for authentication method;

!# In addition to "trust", there are "peer", "ident ", "md5", "password", etc.

85 # IPv4 local connections:

86 host all all 127.0.0.1/32 trust

87 host all all 172.16.200.162/32 trust

4. Related commands

#View status pg_ctl -D /u01/data/postgres/data/ -l /u01/data/postgres/logs/logfile status

 !# Stop service

 pg_ctl -D /u01/data/postgres/data/ -l /u01/data/postgres/ logs/logfile stop

 start service

 pg_ctl -D /u01/data/postgres/data/ -l /u01/data/postgres/logs/logfile start

5. Test

You can use pgadmin to connect to postgresql

pgadmin download address: https://www.pgadmin.org/download/

Step#3, use

1. Simple command

Create user

postgres=# create user postuser1 with password'user1@123';

Create a database

and specify the owner of the database at the same time

postgres=# create database postdb1 owner postuser1;

Database empowerment

Without

authorization, the account can only log in to the console postgres=# grant all privileges on database postdb1 to postuser1;

Log in to the database

Use the newly created account to log in to the newly created database at the operating system layer. After logging in, the prompt is "postdb1=>";

if you log in directly with "postgres=#\c postdb1;" under the postgres account, the logged-in user is still postgres ,

-Bash-4.2$ psql -U postuser1 -d postdb1 -h 127.0.0.1 -p 5432

Create Table

postdb1=> create table tb1(
id int primary key,
name VARCHAR(20),
salary real

 );

Insert table

postdb1=> insert into tb1(

 id, name, salary)

 values(

 101,'pankaj', 5000.00

 );

Inquire

postdb1=>select * from tb1;

2. Basic backup

pg_basebackup -h 192.168.1.105-p 5432 -U repl -F p -P -D /u01/data/postgres/data

-h, main library host, -p, main library service port;

 -U, copy user;

 -F, p is the default output format, output data directory and table space have the same layout, t means tar format output;

 -P, same --progress, display the progress;

 -D, output to the specified directory;

because the main library uses md5 authentication, password authentication is required here.

Thanks

Pankaj K.