Linux Debian 9 Configuration postgressql Database

#读者注意: This article can choose not to read the explanation, directly execute the code in 0 of each paragraph

(0): Some concepts (can skip direct use (i) 0 of the code)

1. Client: Psql. PostgreSQL command-line client program that enters the PostgreSQL database at terminal input Psql

2. Account: Unlike our commonly used MySQL, the Postgressql database is not required to select a database account, and PostgreSQL uses your current system account as a database account under Unix-like systems.

By default, at the beginning of creation, Postgres generates a database named Postgres and a database superuser named Postgres, and also generates a Linux system user named Postgres, which we can cat/etc/passwd command to see it.

If you want to use a different database account like MySQL, you need to modify it separately.

(i): Basic configuration

0. Code (do not want to see the details can be tapped)

2. sudo apt install PostgreSQL
4. /etc/init. D/PostgreSQL status
6. /etc/init. D/PostgreSQL start
8. sudo passwd postgres
10. Su - postgres
12. Psql?
14. ALTER USER postgres with PASSWORD ' ABCDEFG ';

1. Installation: sudo apt install PostgreSQL

2. View the status of the PostgreSQL service (run by default, this step can be skipped)

(1) Views:/etc/init.d/postgresql Status

(2) Stop:/etc/init.d/postgresql stop

(3) Start:/etc/init.d/postgresql start

3. PostgreSQL will create an account for the system: Postgres

3. (1): Recommended to change the password for the Postgres account of the Linux account: sudo passwd postgres

4. Enter Postgre account: Su-postgres

5. Login database: Psql

5. (1) The password of the Postgres account to modify PostgreSQL is "ABCDEFG": Alter USER postgres withpassword encrypted ' ABCDEFG ';

(b): Advanced: Create a new account

The following assumes that you have a Linux user "Bob" and you want to create an account for him named "Bob" with the password "ABCDEFG"

0. Code:

1. Create user Bob with password ' ABCDEFG ';
2. Create DATABASE Bob owner Bob;
3. Grant all privileges on database Bob to Bob;?

0.1. In fact, through the first step, your database can be used, and if you change the PostgreSQL for you to create a Postgre account password, it is very safe.

However, we also have the option to use our own database account.

(1) Postgre permissions are too large, you need a more secure account.

(1) Your current system account is Bob, and you want to go directly to the database using Bob, instead of switching to Postgre.

(2) or your program call is not easy to use the Postgre account, you need to create a new database account.

1. Create an account named "Bob" with the password "ABCDEFG": Create user Bob with password ' ABCDEFG ';

2. Create a database that belongs to Bob (not omitted, must be a database with the same name, cannot change the database name to another):

Create database Bob owner Bob;

3. Empowering: Grant all privileges on database Bob to Bob;

4. Note:

(1) usually from the same name as the Linux user name database account using the account, do not need to enter the database password, your database password is usually used for third-party program connection, such as database visualization software Dbevar, such as remote connection, for example, your own script.

(c) Open the local account login mode:

0. Code:

1. sudo vim /etc/PostgreSQL/*/main/pg_hba.conf
2. Change local all Postgres peer to local all Postgres MD5,WQ exit
3. Sudo/etc/init.d/postgresql Reload
4. Psql-u Postgres

1. Modify the local machine permissions of the PostgreSQL configuration so that all users can cross-enter

sudo vim/etc/postgresql/*/main/pg_hba.conf
Change local all Postgres peer to local all Postgres MD5,WQ exit

(If you change to trust, you do not need to enter a password, but this is not recommended)

2. Overload configuration: Sudo/etc/init.d/postgresql Reload

3. Add parameter-u to set the user entering the warehouse (here is Postgres): Psql-u postgres, then enter the password.

(iv) Allow remote logins:

0. Code:

2. sudo vim /etc/PostgreSQL/*/main/postgresql.conf
4. Search file listen_addresses, comments removed, value changed to *,WQ exit
6. sudo vim/etc/postgresql/*/main/Pg_hba. conf
8. Add one line at the bottom:host all 0.0. 0.0/0 MD5, Wq exit
10. sudo /etc/init. D/PostgreSQL reload

1. Modify the two configuration files:

(1) Postgresql.conf:sudo vim/etc/postgresql/*/main/postgresql.conf,

Search file listen_addresses, comments removed, value changed to *,WQ exit

(2) Pg_hba.conf:sudo vim/etc/postgresql/*/main/pg_hba.conf

Add a line at the bottom: host all 0.0.0.0/0 MD5, WQ exit

(3) Overload configuration: Sudo/etc/init.d/postgresql Reload

(v) Common directives:

1. Login Instructions:

(1)-U designated user

(2)-D Specify the database,

(3)-H Specify the server,

(4)-p specifies the port.

This can be used:

Full login command: psql-u dbuser-d somedb-h 127.0.0.1-p 5432

Log in to a Linux system username for a warehouse with the same name: Psql somedb

Repository with the same name as an account that is logged into the Linux system user name: Psql

2. Console command:

If you used to be a MySQL user, you would regret to find some MySQL like show databases; Show Tables;use Somedatabase and other commands are not feasible in PostgreSQL.

• \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.

3. Database command:

Almost all of the SQL commands are generic and are used by the people themselves.

Linux Debian 9 Configuration postgressql Database