Shulou(Shulou.com)06/02 Report--

This article mainly explains the "Centos7 installation and configuration of Postgresql database and Navicat connection", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "Centos7 installation and configuration of Postgresql database and Navicat connection" bar!

Download and install the Postgresql of the Linux Red Hat family system on the Postgresql official website:

Https://www.postgresql.org/download/linux/redhat/

1. Install the latest version

To go to the URL, you can follow the prompts to install the latest Postgresql11:

Corresponding shell code:

Yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpmyum install postgresql11yum install postgresql11-server/usr/pgsql-11/bin/postgresql-11-setup initdb / / initializes the database for the first time. You can only initialize systemctl enable postgresql-11 / / enable the system login self-starting systemctl start postgresql-11 / / enable service.

two。 Install Postgresql included in the Linux distribution

Install Postgresql9.2 in the Centos7 distribution:

Yum install postgresql-serverpostgresql-setup initdb / / initialize the database for the first time. You can only initialize systemctl enable postgresql.service / / once to enable self-startup, but do not enable it. However, you need to enable the service systemctl start postgresql.service / / for each use.

Shut down the service: systemctl stop postgresql.service

Restart the service: systemctl restart postgresql.service

3. [supplement] how to initialize again

If you execute the initialization command again directly, you will get an error:

Because the data folder under the Postgresql default path / var/lib/pgsql/ is not empty

Check it out: ll-lhtr / var/lib/pgsql/data

Delete all files inside: rm-rf / var/lib/pgsql/data/*

Rerun initialization: postgresql-setup initdb

4. Add new users and new databases for Postgresql

After the initial installation, a database named postgres and a database user named postgres are generated by default. At the same time, a Linux system user named postgres is generated.

(1) use postgres users to generate other users and new databases.

Switch to postgres user: # su-postgres

Next, log in to the PostgreSQL console using the psql command:

At this time, it is equivalent to the system user postgres to log in to the database as a database user with the same name, which does not need to enter a password. If everything is all right, the system prompt will change to "postgres=#", indicating that you have entered the database console.

First use the\ password postgres command to set a password for the postgres user:

(or use the command alter user postgres password 'YourPassword'; to set the password for user postgres)

Exit using the\ Q command (or press ctrl+D directly). You need a password to log in again:

Create a database user only, not a corresponding linux user, and create a user with the name testuser:

CREATE USER testuser WITH PASSWORD 'abc'

Create a test database named test1: create database test1

(here, to create a user database, you can specify the owner: CREATE DATABASE test1 OWNER testuser;)

Give all permissions to the test1 database to testuser, otherwise testuser can only log on to the console and does not have any database operation rights.

GRANT ALL PRIVILEGES ON DATABASE test1 to testuser

Final test:

5. [supplement] basic commands for Postgresql database

Login command: psql-h 127.0.0.1-U testuser-d test1-p 5432

The psql command parameter description:

-h: database IP-U: login user-d: database logged in-p: login port

\ dt: lists all tables in the current database

List table name

SELECT tablename FROM pg_tables;WHERE tablename NOT LIKE 'pg%'AND tablename NOT LIKE' sql_%' ORDER BY tablename

\ l: list the database name (or SELECT datname FROM pg_database;)

\ C [Database name]: switch database

\ d [Database]: get the names of all tables

\ d [Table name]: get the table structure

Query through SQL statement

Select * from pg_tables: get the information about all the tables in the current db (where pg_tables is the system view)

Select tablename from pg_tables where schemaname='public': gets the names of all user-defined tables (where the "tablename" field is the name of the table and "schemaname" is the name of schema. User-defined tables are placed under the schema named public by default if they are not specially handled.

6. Configure third party users to log in to the database

After the above configuration, the database can only be accessed by entering the linux user with the corresponding database name, otherwise an Ident error is reported and the Navicat cannot be connected successfully.

Find the configuration file of the database: # find /-name pg_hba.conf

Open: # vi / var/lib/pgsql/data/pg_hba.conf

Modify the peer and ident of the three uncommented verification methods to md5:

[description]:

METHOD specifies how to handle client authentication. The commonly used ones are ident,md5,password,trust,reject.

Ident is the default local authentication method for PostgreSQL under Linux. Operating system users who can log in to the server correctly (note: not database users) can use the database users mapped by this user to log in to the database without a password. The user mapping file is pg_ident.conf, which records the database user that matches the operating system user. If an operating system user does not map a user in this file, the default mapping database user has the same name as the operating system user. For example, there is an operating system user named user1 on the server, and there is also a database user with the same name on the database. After logging in to the operating system, user1 can directly enter psql and log in to the database as a user1 database user without a password. Many beginners will encounter the psql-U username login database but there is a "username ident authentication failure" error, clearly the database user has createuser. The reason is that ident authentication is used, but there is no operating system user with the same name or corresponding mapping user. Solution:

1. Add mapped users to pg_ident.conf

2. Change the authentication method:

Md5 is a commonly used password authentication method, if you do not use ident, it is best to use md5. The password is transmitted to the database in the form of md5, which is more secure and does not require the establishment of an operating system user with the same name.

Password is sent to the database with a clear text password and is not recommended for use in a production environment.

Trust can log in without a password or ident as long as you know the database user name, and it is not recommended to use it in a production environment.

Reject is denying authentication.

Look for listen_addresses in the file, and his value indicates that if you want to access the PostgreSQL database only from the local computer, set this item to localhost

If you want to access the PostgreSQL database from the local area network, set this item to the LAN IP address of the PostgreSQL database

If you want to access the PostgreSQL database from the Internet, set this item to the Internet IP address of the PostgreSQL database

If you want to access the PostgreSQL database from anywhere, set this configuration item to "*"

7. Configure Navicat visual management tools

List all ports: netstat-ntlp

Fill in the port and password to test the connection. If an Ident error is reported, please perform step 6, and then click OK.

Thank you for reading, the above is the content of "Centos7 installation and configuration of Postgresql database and Navicat connection". After the study of this article, I believe you have a deeper understanding of the problem of Centos7 installation and configuration of Postgresql database and Navicat connection, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.