Introduction to PostgreSQL Novice

Since MySQL was acquired by Oracle, PostgreSQL has become the first choice for open-source relational databases.

This article describes the installation and basic usage of PostgreSQL for first-time users to get started. The following is based on the Debian operating system, and the rest of the operating system is not in the balance, but most of the content should apply.

First, installation

First, install the PostgreSQL client.

sudo apt-get install postgresql-client

Then, install the PostgreSQL server.

sudo apt-get install PostgreSQL

Normally, when the installation is complete, the PostgreSQL server will automatically open on the 5432 port of this machine.

If you also want to install the graphical management interface, you can run the following command, but this article does not cover this aspect.

sudo apt-get install pgadmin3

Ii. adding new users and new databases

After initial 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.

Below, we use Postgres users to generate additional users and new databases. There are several ways to achieve this, and here are two.

The first method, use the PostgreSQL console.

First of all, create a new Linux user, you can take the name you want, here is dbuser.

sudo adduser dbuser

Then, switch to the Postgres user.

sudo su-postgres

Next, log in to the PostgreSQL console using the Psql command.

Psql

This is equivalent to the system user Postgres with the same name as the database user, log in to the database, this is not input 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

After the PostgreSQL database is installed, the default is to accept only local access connections. If you want to access the PostgreSQL database server on a different host, you need to configure it accordingly.

The steps to configure the remote connection PostgreSQL database are simple and only need to modify the pg_hba.conf and postgresql.conf in the data directory .

pg_hba.conf: Configure access to the database,

postgresql.conf: configures the appropriate parameters for the PostgreSQL database server.

Here are the steps to configure:

1. Modify the pg_hba.conf file to configure the user's access rights (#开头的行是注释内容):

1. # TYPE DATABASE USER cidr-address METHOD
2. # "Local" is for Unix domain sockets connections only
3. Local all All trust
4. # IPV4 Local connections:
5. Host All 127.0.0.1/32 Trust
6. Host All 192.168.1.0/24 MD5
7. # IPV6 Local connections:
8. Host all:: 1/128 Trust
9. # to allow your client visiting PostgreSQL server

10. Host all 0.0.0.0 0.0.0.0 MD #添加允许所有IP连接

7th is the newly added content that allows all hosts on the network segment 192.168.1.0 to access the database using all legitimate database usernames and provide encrypted password authentication.

Where the number 24 is the subnet mask, which means that the 192.168.1.0--192.168.1.255 computer is allowed access!

2. Modify the postgresql.conf file to modify the listening mode of the database server to listen for connection requests made by all hosts.

Navigate to #listen_addresses= ' localhost '. After the PostgreSQL installation is complete, the default is to accept only connection requests that come in native localhost.

Remove line start # and modify line content to listen_addresses= ' * ' to allow the database server to listen for connection requests from any host

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

Third, login 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

Four, 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.

V. Operation of the database

The basic database operation is to use the general SQL language.

# Create a new table
CREATE TABLE user_tbl (name VARCHAR (), signup_date date);

# Insert Data
INSERT into USER_TBL (name, signup_date) VALUES (' Zhang San ', ' 2013-12-22 ');

# Select Record
SELECT * from USER_TBL;

# Update Data
UPDATE user_tbl Set name = ' John Doe ' WHERE name = ' Zhang San ';

# Delete Records
DELETE from user_tbl WHERE name = ' John Doe ';

# Add Fields
ALTER TABLE user_tbl ADD email VARCHAR (40);

# Update Structure
Alter TABLE USER_TBL ALTER COLUMN signup_date SET not NULL;

# rename Field
ALTER TABLE user_tbl RENAME COLUMN signup_date to signup;

# Delete Fields
ALTER TABLE user_tbl DROP COLUMN email;

# Renaming a table
ALTER TABLE user_tbl RENAME to Backup_tbl;

# Delete a table
DROP TABLE IF EXISTS backup_tbl;

Introduction to PostgreSQL Novice