PostgreSQL tutorial (17): client commands (1), postgresql tutorial

PostgreSQL tutorial (17): client commands (1), postgresql tutorial

0. Password File:

Before providing other PostgreSQL client commands, we need to first introduce the password file in PostgreSQL. The reason why this file is described in advance is that we will apply this file a lot in the sample code below to ensure that our scripts can be automated. In other words, if the file is not provided during Client Command Execution, all client commands of PostgreSQL will be interrupted when the password is entered.
In the current user's HOME directory, We need to manually create the file name. pgpass password file, so that when we connect to the PostgreSQL server, the client command automatically reads the file and obtains the password information required for logon. The file format is as follows:
 Copy codeThe Code is as follows:
Hostname: port: database: username: password
 
The above data is separated by a colon (:), which is divided into five fields, indicating the server host name (IP), server listening port number, database name accessed by login, login user name, and password, the first four fields can be matched by an asterisk. See the following example:
 Copy codeThe Code is as follows:
/> Cat>. pgpass
*: 5432: postgres: 123456
CTRL + D
#. The permission of the pgpass file must be 0600 to prevent access by any global users or users in the same group. Otherwise, the file will be ignored.
/> Chmod 0600. pgpass
 
Before learning the subsequent client commands, We need to manually create the file according to our application environment, so that all subsequent sample codes will use the password file, in this way, they can all be automatically completed in batches.

1. createdb:

Create a new PostgreSQL database. The command is used as follows:
 Copy codeThe Code is as follows:
Createdb [option...] [dbname] [description]
 
1. List of command line options:

Option	Description
-D (-- tablespace = tablespace)	Specifies the default tablespace of the database.
-E (-- echo)	Echo the command generated by createdb and send it to the server.
-E (-- encoding = encoding)	Specifies the character encoding method used for this database.
-L (-- locale = locale)	Specifies the localization settings for this database.
-O (-- owner = owner)	Specifies the owner of the new database. If this option is not specified, the value is the user currently logged on.
-T (-- template = template)	Specifies the template database for creating this database.
-H (-- host = host)	Specify the Host Name of the PostgreSQL server.
-P (-- port = port)	Specifies the listening port of the server. If this parameter is not specified, the default port 5432 is used.
-U (-- username = username)	The login user name for this operation. If the-O option is not specified, the Owner of this database will be the login user.
-W (-- no-password)	If the current logon user does not have a password, you can specify this option to log on directly.

2. Application Example:
Copy codeThe Code is as follows:
#1. Log On As postgres. (For details, refer to the above Password File)
/> Psql
#2. Create a tablespace.
S = # create tablespace my_tablespace LOCATION '/opt/PostgreSQL/9.1/mydata ';
CREATE TABLESPACE
#3. Create the owner of the new database.
S = # create role myuser login password '000000 ';
CREATE ROLE
Postgres = # \ q
#4. Create a new database. The Login User for this connection is postgres, the owner of the new database is myuser, The tablespace is my_tablespace, and the new database name is mydatabase.
/> Createdb-U postgres-O myuser-D my_tablespace-e mydatabase
Create database mydatabase OWNER myuser TABLESPACE my_tablespace;
#5. Log On again and check whether the database is successfully created and whether the tablespace is consistent with the owner in the query system table.
/> Psql
Postgres = # SELECT datname, rolname, spcname FROM pg_database db, pg_authid au, pg_tablespace ts WHERE datname = 'mydatabase' AND datdba = au. oid AND dattablespace = ts. oid;
Datname | rolname | spcname
------------ + --------- + ---------------
Mydatabase | myuser | my_tablespace
(1 row)

Ii. dropdb:

Delete an existing PostgreSQL database.
 Copy codeThe Code is as follows:
Dropdb [option...] dbname
 
1. List of command line options:

 

Option	Description
-E (-- echo)	Echo the command generated by dropdb and send it to the server.
-I (-- interactive)	Prompt before any destructive action.
-Q (-- quiet)	No response is displayed.
-H (-- host = host)	Specify the Host Name of the PostgreSQL server.
-P (-- port = port)	Specifies the listening port of the server. If this parameter is not specified, the default port 5432 is used.
-U (-- username = username)	The logon username for this operation.
-W (-- no-password)	If the current logon user does not have a password, you can specify this option to log on directly.

2. Application Example:
Copy codeThe Code is as follows:
# Connect to the server as postgres and delete the mydatabase database.
/> Dropdb-U postgres-e mydatabase
Drop database mydatabase;
# Check the system table to verify whether the database has been deleted.
/> Psql
S = # SELECT count (*) FROM pg_database WHERE datname = 'mydatabase ';
Count
-------
0
(1 row)

Iii. reindexdb:

Creates an index for a specified PostgreSQL database.
 Copy codeThe Code is as follows:
Reindexdb [connection-option...] [-- table |-t table] [-- index |-I index] [dbname]
Reindexdb [connection-option...] [-- all |-a]
Reindexdb [connection-option...] [-- system |-s] [dbname]
 
1. List of command line options:

 

Option	Description
-A (-all)	Rebuild the index of the entire database.
-E (-- echo)	Echo the command generated by reindexdb and send it to the server.
-I (-- index = index)	Only the specified index is rebuilt.
-Q (-- quiet)	No response is displayed.
-S (-- system)	Rebuild the index of the database system table.
-T (-- table = table)	Only the indexes of the specified data table are rebuilt.
-H (-- host = host)	Specify the Host Name of the PostgreSQL server.
-P (-- port = port)	Specifies the listening port of the server. If this parameter is not specified, the default port 5432 is used.
-U (-- username = username)	The logon username for this operation.
-W (-- no-password)	If the current logon user does not have a password, you can specify this option to log on directly.

2. Application Example:
 Copy codeThe Code is as follows:
# Only re-create all indexes on the testtable.
/> Reindexdb-t testtable-e-U postgres
Reindex table testtable;
# Only rebuilding the specified index testtable_idx
/> Reindexdb-I testtable_idx-e-U postgres
Reindex index testtable_idx;
# Re-create all indexes of the specified database mydatabase.
/> Reindexdb mydatabase

Iv. vacuumdb:

Collect garbage and analyze a PostgreSQL database.
 Copy codeThe Code is as follows:
Vacuumdb [-options] [-- full |-f] [-- verbose |-v] [-- analyze |-z] [-t table [(column [,...])] [dbname]
Vacuumdb [-options] [-- all |-a] [-- full |-f] [-- verbose |-v] [-- analyze |-z]
 
1. List of command line options:
 

Option	Description
-A (-- all)	Clean up all databases.
-E (-- echo)	Echo the command generated by vacuumdb and send it to the server.
-F (-- full)	Complete cleanup.
-Q (-- quiet)	No response is displayed.
-TTable[(Column[,...])]	Only the specified data table is cleared or analyzed. The field name must be declared only when used in conjunction with the -- analyze option.
-V (-- verbose)	Print the detailed information during processing.
-Z (-- analyze)	Calculates the statistical value used by the scheduler.
-H (-- host = host)	Specify the Host Name of the PostgreSQL server.
-P (-- port = port)	Specifies the listening port of the server. If this parameter is not specified, the default port 5432 is used.
-U (-- username = username)	The logon username for this operation.
-W (-- no-password)	If the current logon user does not have a password, you can specify this option to log on directly.

2. Application Example:
Copy codeThe Code is as follows:
# Clear the entire database mydatabase.
/> Vacuumdb-e mydatabase
VACUUM;
# Clear and analyze the testtable table in postgres database.
/> Vacuumdb-e -- analyze -- table 'testtable' postgres
Vacuum analyze testtable;
# Clear and analyze the I FIELD OF THE testtable table in ipvs database.
/> Vacuumdb-e -- analyze-t 'testtable (I) 'postgres
Vacuum analyze testtable (I );
 

V. createuser:

To define a new PostgreSQL user account, you must note that only super users or users with the CREATEROLE permission can execute this command. If you want to create a super user, you can only execute this command as a Super User. In other words, a common user with the CREATEROLE permission cannot create a Super User. The command is used as follows:
 Copy codeThe Code is as follows:
Createuser [option...] [username]
 
1. List of command line options:

Option	Description
-C number	Set the maximum number of connections for the newly created user. The default value is no limit.
-D (-- createdb)	Allows the new user to create a database.
-D (-- no-createdb)	This user is prohibited from creating databases.
-E (-- echo)	Echo the command generated by createuser and send it to the server.
-E (-- encrypted)	Encrypt the user password stored in the database. If no declaration is made, the default value is used.
-I (-- inherit)	The newly created role automatically inherits the permissions of its group roles.
-I (-- no-inherit)	The newly created role does not automatically inherit the permissions of its group roles.
-L (-- login)	The new role is granted the logon permission. This option is the default option.
-L (-- no-login)	The new role is not granted the logon permission.
-N (-- unencrypted)	Do not encrypt the user password stored in the database. If no declaration is made, the default value is used.
-P (-- pwprompt)	If this option is provided, you will be prompted to set a password when creating a user.
-R (-- createrole)	The new role is granted the permission to create a database.
-R (-- no-createrole)	The new role is not authorized to create a database.
-S (-- superuser)	The new role is a superuser.
-S (-- no-superuser)	The new role is not a superuser.
-H (-- host = host)	Specify the Host Name of the PostgreSQL server.
-P (-- port = port)	Specifies the listening port of the server. If this parameter is not specified, the default port 5432 is used.
-U (-- username = username)	The logon username for this operation.
-W (-- no-password)	If the current logon user does not have a password, you can specify this option to log on directly.

2. Application Example:
Copy codeThe Code is as follows:
# For some options that do not have the default settings, such as-(d/D),-(s/S), and-(r/R), if it is not directly specified in the command line, A prompt is displayed when you execute this command.
# Note that the prompt will suspend the automation script until the command is entered.
/> Createuser-U postgres myuser
Shall the new role be a superuser? (Y/n) n
Shall the new role be allowed to create databases? (Y/n) y
Shall the new role be allowed to create more new roles? (Y/n) n
Create role myuser nosuperuser createdb nocreaterole inherit login;
# Log on to the psql console and check the System View to verify whether the user has been successfully created and whether the permissions of the new role are correct.
/> Psql
Postgres = # SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles WHERE rolname = 'myuser ';
Rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin
--------- + ---------- + ------------ + --------------- + -------------
Myuser | f | t
(1 row)
# To ensure that the automation script is not suspended by the prompt of this command, we need to specify all options without default values when executing this command.
/> Createuser-U postgres-e-S-D-R myuser2
Create role myuser2 nosuperuser nocreatedb nocreaterole inherit login;
# When creating a user, you can specify the password of the user immediately. This operation is completed by the-P option. However, the automatic script will be suspended,
# Therefore, we can adopt a compromise method, that is, when creating a user, no password is specified, and the user's password is manually created after the automated script is successfully executed.
/> Createuser-P-s-e myuser3
Enter password for new role:
Enter it again:
Create role myuser3 PASSWORD 'md5fe54c4f3129f2a766f53e4f4c9d2a698 'superuser createdb createrole inherit login;

6. dropuser:

To delete a PostgreSQL user account, you must note that only a Super User or a user with the CREATEROLE permission can execute this command. to delete a Super User, you can only execute this command as a Super User. The command is used as follows:
 Copy codeThe Code is as follows:
Dropuser [option...] [username]
 
1. List of command line options:

 

Option	Description
-E (-- echo)	Echo the command generated by dropuser and send it to the server.
-I (-- interactive)	Prompt before any destructive action.
-H (-- host = host)	Specify the Host Name of the PostgreSQL server.
-P (-- port = port)	Specifies the listening port of the server. If this parameter is not specified, the default port 5432 is used.
-U (-- username = username)	The logon username for this operation.

2. Application Example:
Copy codeThe Code is as follows:
# Directly delete a specified user.
/> Dropuser-e myuser3
Drop role myuser3;
# When you delete a specified user, this command will provide a prompt to avoid misoperation.
/> Dropuser-e-I myuser2
Role "myuser2" will be permanently removed.
Are you sure? (Y/n) y
Drop role myuser2;