02-Basic use of PostgreSQL

1. Database operation

①: Log in to the database

psql -U postgres -d postgres -h 127.0.0.1

②: View all databases

\l

③: Create database

# Create a database named mydb
create database mydb;

④:Switch database

# \c database name
\c mydb

⑤: Delete database

# Before deleting, make sure the database is not connected
drop database mydb;

2. Database table operations

①: Numeric type

②: Data table operation

1. Create table

1. The difference between sequence, serial and identity among the three auto-increment columns in PostgreSQL

2. Create table

create table test(id serial primary key, name varchar(255));

2. Insert data into the table

insert into test(name) values('Anne');

3. View all tables under the current database

\d

4. View the table structure, equivalent to desc

\d test(surface)

5. Query all data

3. Schema

①: Introduction

PostgreSQL schema (Schema) can be understood as a collection of tables (similar to Oracle’s table space concept).

A schema can contain views, indexes, data types, functions and operators, etc.

The same object name can be used in different schemas without conflict, for example schema1 and myschema can both contain a table named mytable.

Advantages of using patterns:

1. Allow multiple users to use a database without interfering with each other
2. Organize database objects into logical groups for easier management
3. Objects of third-party applications can be placed in separate schemas so that they do not conflict with the names of other objects

Schemas are similar to operating system level directories, but schemas cannot be nested.

②: Create a new Schema

1. Before creation (there is a default public) and the test table created before is also included

2. Create a new Schema

create schema newSchema;

3. Create a table test in the new Schema

create table newSchema.test(id serial primary key, name varchar(255));

4. How to back up PostgreSQL database

If you are using PostgreSQL in a production environment, be sure to take precautions to ensure that your users’ data is not lost.

①：Must-see

1. Login

2. Note that operations must be performed in the shell console instead of the database console

②: Single database

PostgreSQL provides the pg_dump utility to simplify the process of backing up a single database. This command must be run as a user with read permissions on the database to be backed up.

1. Backup

There are several backup format options:

• *.bak: compressed binary format

• *.sq1: Clear text dump

• *.tar: tarball

#Backup database
$ pg_dump -U postgres -f /tmp/postgres.sql postgres (export the postgres database and save it as postgres.sq1)
$ pg_dump -U postgres -f /tmp/postgres.sql-t test postgres (export the data of table test in the postgres database)
$ pg_dump -U postgres -F t -f /tmp/postgres.tar postgres (export the postgres database and compress it in tar format and save it as postgres.tar)

#Restore database
$ psql -U postgres -f /tmp/postgres.sql bk01 (restore postgres.sq1 data to bk01 database)
#pg_restore--Restore the PostgreSQL database from the backup file created by pg_dump. Used to restore the PostgreSQL database in any non-plain text format dumped by pg_dump.
$ pg_restore-Upostgres-dbk01/tmp/postgres.tar (restore postgres.tar data to bk01 database)

1. Dump the contents of the database to a file by running the following command. Replace dbname with the name of the database to be backed up.

 # Specify the file output directory through -f
    pg_dump -f /usr/local/src/mydb.sql mydb;

2. Recovery

1. Delete the backed up mydb database

2. Create a new empty database (mydb2)

3. Restore the database (-f specifies the backup file path)

 psql -U postgres -f /usr/local/src/mydb.sql mydb2;

You can see that the data recovery was successful

③：All databases

5. User operations

#View users
\du

#Create user and set password
CREATE USER 'username' WITH PASSWORD 'password' ;
CREATE USER test WITH PASSWORD 'test';

#Modify user password
$ ALTER USER ' username' WITH PASSWORD ' password' ;

#Database authorization, giving the specified account all permissions to the specified database
$ GRANT ALL PRIVILEGES ON DATABASE ' dbname' TO ' username' ;

#Grant database mydb permissions to test
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;

#But at this time the user still does not have read and write permissions and needs to continue the authorization form
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;

#Note that the sq1 statement must be executed in the database to be operated
#Remove all permissions of the specified database for the specified account
REVOKE ALL PRIVILEGES ON DATABASE mydb from test

#delete users
drop user test

①: Create a user and set a password

1. Note: First switch to the database that needs to be operated

2. View all users

2. Create user

create user coke with password 'coke';

②: Modify user password

1. Change password

alter user coke with password 'new_coke';

2. Log in with new password

psql -U coke -d mydb2 -h 127.0.0.1;

3. Execute query sql

Newly created users do not have any permissions and need to be authorized

③: Database authorization

1. Database authorization, giving the specified account all permissions to the specified database

grant all privileges on database mydb2 to coke;

2. However, the user still does not have read and write permissions at this time and needs to continue the authorization form

grant all privileges on all tables in schema public to coke;

3. Test (already has permission)

④: Remove user data table permissions

revoke all privileges on database mydb2 from coke;

④: Remove user database permissions

revoke all privileges on database mydb2 from coke;

⑤: Delete user

drop user coke;