PostgreSQL Tutorial (17): Client command (1) _postgresql

0, password file:

Before giving other PostgreSQL client commands, we need to introduce the password file in PostgreSQL. The file is explained here in advance because we are applying the file in a lot of the following sample code to ensure that our scripts are automated. In other words, if the file is not supplied when the client command executes, all client commands for PostgreSQL are interrupted by a password input prompt.
In the current user's home directory, we need to manually create a password file named. pgpass, so that when we connect to the PostgreSQL server, the client commands automatically read the password information that the file is required to log in. The format of the file is as follows:

Copy Code code as follows:

Hostname:port:database:username:password

The above data is separated by a colon, which is divided into five fields, representing the server host name (IP), the port number that the server listens to, the database name of the login access, the login username, and the password, where the first four fields can use an asterisk (*) to represent any value. See the following example:

Copy Code code as follows:

/> Cat > Pgpass
*:5432:postgres:postgres:123456
Ctrl+d
The #.pgpass file must have a permission of 0600 to prevent any global or group of users from accessing it, otherwise the file will be ignored.
/> chmod 0600. Pgpass

Before we learn the client commands later, we need to create the file manually according to our application environment so that all of the code in the following example will use the password file so that they can be automatically completed in batches.

First, Createdb:

Create a new PostgreSQL database. The command is used in the following ways:

Copy Code code as follows:

createdb [option ...] [dbname] [Description]

1. List of command line options:

Options	Description
-D (--tablespace=tablespace)	Specifies the default table space for the database.
-E (--echo)	echo createdb the generated command and sends it to the server.
-E (--encoding=encoding)	Specifies the type of character encoding used for this database.
-L (--locale=locale)	Specifies the localized settings for this database.
-O (--owner=owner)	Specifies the owner of the new database, if this option is not specified, this is the currently logged-on user.
-T (--template=template)	Specifies the template database in which this database is created.
-H (--host=host)	Specifies the host name of the PostgreSQL server.
-P (--port=port)	Specifies the listening port for the server, or the default 5432 if unspecified.
-U (--username=username)	The login username for this operation, and owner of this database will be the logged-on user if the-o option is not specified.
-W (--no-password)	If the current logged-on user does not have a password, you can specify this option to log on directly.

2. Application Example:

Copy Code code as follows:

#1. Log in as Postgres. (Details refer to the contents of the password file above)
/> Psql
#2. Create a table space.
postgres=# CREATE tablespace my_tablespace LOCATION '/opt/postgresql/9.1/mydata ';
CREATE tablespace
#3. Owner of the new database is created.
postgres=# CREATE role myuser LOGIN PASSWORD ' 123456 ';
CREATE role
postgres=# \q
#4. Create a new database where the logged-in user for this connection is postgres, the new database owner is myuser, the table space is My_tablespace, and the new database is named MyDatabase.
/> createdb-u postgres-o myuser-d my_tablespace-e mydatabase
CREATE DATABASE mydatabase OWNER myuser tablespace my_tablespace;
#5. Log back in to see if the database was created successfully by querying the system table, and whether the table space and owner are consistent.
/> 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)

Second, dropdb:

Deletes an existing PostgreSQL database.

Copy Code code as follows:

dropdb [option ...] dbname

1. List of command line options:

Options	Description
-E (--echo)	echo dropdb the generated command and sends it to the server.
-I (--interactive)	Prompt before doing any destructive action.
-Q (--quiet)	The response is not displayed.
-H (--host=host)	Specifies the host name of the PostgreSQL server.
-P (--port=port)	Specifies the listener port for the server, or the default 5432 if unspecified.
-U (--username=username)	The login user name for this operation.
-W (--no-password)	If the current logged-on user does not have a password, you can specify this option to log on directly.

2. Application Example:

Copy Code code as follows:

#以postgres的身份连接服务器, delete the MyDatabase database.
/> Dropdb-u postgres-e MyDatabase
DROP DATABASE MyDatabase;
#通过查看系统表验证该数据库是否已经被删除.
/> Psql
postgres=# SELECT Count (*) from pg_database WHERE datname = ' mydatabase ';
Count
-------
0
(1 row)

Third, Reindexdb:

Rebuilds the index for a specified PostgreSQL database.

Copy Code code 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:

Options	Description
-A (-all)	Rebuilds the index of the entire database.
-E (--echo)	echo reindexdb the generated command and sends it to the server.
-I (--index=index)	Only the specified index is rebuilt.
-Q (--quiet)	The response is not displayed.
-S (--system)	Rebuilds the index of the database system table.
-T (--table=table)	Rebuilds only the indexes of the specified data table.
-H (--host=host)	Specifies the host name of the PostgreSQL server.
-P (--port=port)	Specifies the listener port for the server, or the default 5432 if unspecified.
-U (--username=username)	The login user name for this operation.
-W (--no-password)	If the current logged-on user does not have a password, you can specify this option to log on directly.

2. Application Example:

Copy Code code as follows:

#仅重建数据表testtable上的全部索引.
/> Reindexdb-t testtable-e-u postgres postgres
REINDEX TABLE testtable;
#仅重建指定索引testtable_idx
/> Reindexdb-i testtable_idx-e-u postgres postgres
REINDEX INDEX Testtable_idx;
#重建指定数据库mydatabase的全部索引.
/> Reindexdb MyDatabase

Four, Vacuumdb:

Collect garbage and analyze a PostgreSQL database.

Copy Code code 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:

Options	Description
-A (--all)	Clean all databases.
-E (--echo)	echo vacuumdb the generated command and sends it to the server.
-F (--full)	Perform a full cleanup.
-Q (--quiet)	The response is not displayed.
-T table [(column[,...])]	Only the specified datasheet is cleaned or parsed, and the field name needs to be declared only when used in conjunction with the--analyze option.
-V (--verbose)	Print details during the process.
-Z (--analyze)	Calculates the statistical value used for the planner.
-H (--host=host)	Specifies the host name of the PostgreSQL server.
-P (--port=port)	Specifies the listener port for the server, or the default 5432 if unspecified.
-U (--username=username)	The login user name for this operation.
-W (--no-password)	If the current logged-on user does not have a password, you can specify this option to log on directly.

2. Application Example:

Copy Code code as follows:

#清理整个数据库mydatabase.
/> Vacuumdb-e MyDatabase
VACUUM;
#清理并分析postgres数据库中的testtable表.
/> vacuumdb-e--analyze--table ' testtable ' Postgres
VACUUM ANALYZE testtable;
#清理并分析postgres数据库中的testtable表的i字段.
/> vacuumdb-e--analyze-t ' testtable (i) ' Postgres
VACUUM ANALYZE testtable (i);

Five, CreateUser:

Define a new PostgreSQL user account, which requires that only superuser or a user with Createrole permissions can execute the command. If you want to create a superuser, you can only execute the command as Superuser, in other words, a normal user with Createrole permissions cannot create a superuser. The command is used in the following ways:

Copy Code code as follows:

CreateUser [option ...] [Username]

1. List of command line options:

Options	Description
-C Number	Sets the maximum number of connections for newly created users, with no restrictions by default.
-D (--createdb)	Allows the new user to create a database.
-D (--no-createdb)	Prevents the new user from creating the database.
-E (--echo)	echo createuser the generated command and sends it to the server.
-E (--encrypted)	Encrypt the password for the user stored in the database. If there is no declaration, 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 will be granted logon rights, which is the default option.
-L (--no-login)	The new role is not granted logon rights.
-N (--unencrypted)	The password for the user stored in the database is not encrypted. If there is no declaration, the default value is used.
-P (--pwprompt)	If you give this option, you will be prompted to set the password when you create the user.
-R (--createrole)	The new role is granted permission to create the database.
-R (--no-createrole)	The new role is not granted permission to create the database.
-S (--superuser)	The new role is superuser.
-S (--no-superuser)	The new role is not superuser.
-H (--host=host)	Specifies the host name of the PostgreSQL server.
-P (--port=port)	Specifies the listener port for the server, or the default 5432 if unspecified.
-U (--username=username)	The login user name for this operation.
-W (--no-password)	If the current logged-on user does not have a password, you can specify this option to log on directly.

2. Application Example:

Copy Code code as follows:

# for some options that have no default settings, such as-(D/D),-(S/S), and-(R/R), if not specified directly on the command line, the prompt will be given if the command is executed.
# It is important to note that the prompt will suspend the automation script until the command continues to execute.
/> Createuser-u Postgres MyUser
Shall The new role is a superuser? (y/n) n
Shall the new role is allowed to create databases? (y/n) Y
Shall the new role is allowed to create more new roles? (y/n) n
CREATE role myuser nosuperuser createdb nocreaterole INHERIT LOGIN;
# View the system view after logging in Psql to verify that the user was created successfully and that the new role has the correct permissions.
/> Psql
postgres=# SELECT rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin from pg_roles WHERE rolname = ' MyUser ';
Rolname | Rolsuper | Rolinherit | Rolcreaterole | Rolcreatedb | Rolcanlogin
---------+----------+------------+---------------+-------------+-------------
MyUser | f | T | f | T | T
(1 row)
# to ensure that the automation script is not suspended by the prompt for the command, we need to specify all options that do not have a default value when executing the command.
/> Createuser-u postgres-e-s-d-R myuser2
CREATE role Myuser2 nosuperuser nocreatedb nocreaterole INHERIT LOGIN;
# we can specify the user's password as soon as the user is created, which is done by the-p option, but this usage will definitely suspend the automation script.
# so we can use a tradeoff between creating a user without specifying a password, and then manually using the user's password after the automation script executes successfully.
/> Createuser-p-s-e Myuser3
Enter Password for new role:
Enter It again:
CREATE role Myuser3 PASSWORD ' md5fe54c4f3129f2a766f53e4f4c9d2a698 ' superuser createdb createrole LOGIN;

vi. Dropuser:

Deleting a PostgreSQL user account requires that only superuser or a user with Createrole permissions can execute the command, and that if you want to remove a superuser, you can only execute the command through the Power User's identity. The command is used in the following ways:

Copy Code code as follows:

Dropuser [option ...] [Username]

1. List of command line options:

Options	Description
-E (--echo)	echo dropuser the generated command and sends it to the server.
-I (--interactive)	Prompt before doing any destructive action.
-H (--host=host)	Specifies the host name of the PostgreSQL server.
-P (--port=port)	Specifies the listener port for the server, or the default 5432 if unspecified.
-U (--username=username)	The login user name for this operation.

2. Application Example:

Copy Code code as follows:

# deletes the specified user directly.
/> Dropuser-e Myuser3
DROP role Myuser3;
# When you delete a specified user, the command prompts you to avoid misoperation.
/> Dropuser-e-I. myuser2
Role "Myuser2" would be permanently removed.
Are you sure? (y/n) Y
DROP role Myuser2;