PostgreSQL Tutorial (17): Client Commands (1)

    hostname:port:database:username:password
 

    /> cat > .pgpass
    *:5432:postgres:postgres:123456
    CTRL+D
The permissions of #.pgpass file must be 0600, thereby preventing any global or group users from accessing, otherwise this file will be ignored.
    /> chmod 0600 .pgpass
 

    createdb [option...] [dbname] [description]
 

#1. Log in as postgres. (For details, please refer to the content of the password file above)
    /> psql
#2. Create a tablespace.
    postgres=# CREATE TABLESPACE my_tablespace LOCATION '/opt/PostgreSQL/9.1/mydata';
    CREATE TABLESPACE
#3. Create the owner of the new database.
    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 owner of the new database is myuser, the table space is my_tablespace, and the new database is called mydatabase.
    /> createdb -U postgres -O myuser -D my_tablespace -e mydatabase
    CREATE DATABASE mydatabase OWNER myuser TABLESPACE my_tablespace;
#5. Log in again and check the system table to see if the database was created successfully and whether the tablespace 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 = AND dattablespace = ;
      datname   | rolname |    spcname
    ------------+---------+---------------
     mydatabase | myuser  | my_tablespace
    (1 row)

    dropdb [option...] dbname
 

#Connect the server as postgres and delete the mydatabase database.
    /> dropdb -U postgres -e mydatabase
    DROP DATABASE mydatabase;
# Verify that the database has been deleted by viewing the system table.
    /> psql
    postgres=# SELECT count(*) FROM pg_database WHERE datname = 'mydatabase';
     count
    -------
         0
    (1 row)

    reindexdb [connection-option...] [--table | -t table ] [--index | -i index ] [dbname]
    reindexdb [connection-option...] [--all | -a]
    reindexdb [connection-option...] [--system | -s] [dbname]
 

#Reconstruct only all indexes on the data table testtable.
    /> reindexdb -t testtable -e -U postgres postgres
    REINDEX TABLE testtable;
#Rebuild only the specified index testtable_idx
    /> reindexdb -i testtable_idx -e -U postgres postgres
    REINDEX INDEX testtable_idx;   
#Reconstruct all indexes of the specified database mydatabase.
    /> reindexdb mydatabase

    vacuumdb [-options] [--full | -f] [--verbose | -v] [--analyze | -z] [-t table [(column [,...])]] [dbname]
    vacuumdb [-options] [--all | -a] [--full | -f] [--verbose | -v] [--analyze | -z]
 

#Clean the entire database mydatabase.
    /> vacuumdb -e mydatabase
    VACUUM;
#Clean and analyze the testtable table in the postgres database.
    /> vacuumdb -e --analyze --table 'testtable' postgres
    VACUUM ANALYZE testtable;
#Clean and analyze the i field of the testtable table in the postgres database.
    /> vacuumdb -e --analyze -t 'testtable(i)' postgres
    VACUUM ANALYZE testtable(i);
 

    createuser [option...] [username]
 

# For some options that do not have default settings, such as -(d/D), -(s/S) and -(r/R), if not specified directly on the command line, a prompt message will be given when executing the command.
# It should be noted that this prompt will suspend the automation script and the command will not continue to be executed until it 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;
# After logging in through psql, view the system view to verify that the user was 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             | f                  | t                 | t
    (1 row)
# In order to ensure that the automation script will not be suspended by the prompt of this command, we need to specify all options without default values ​​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 immediately when creating the user. This operation is completed by the -P option, but this usage will definitely hang the automation script.
# Therefore, we can adopt a compromise method, that is, do not specify a password when creating a user, and then manually use the user's password 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;

    dropuser [option...] [username]
 

# Delete the specified user directly.
    /> dropuser -e myuser3
    DROP ROLE myuser3;
# When deleting a specified user, this command will give a prompt message to avoid misoperation.
    /> dropuser -e -i myuser2
    Role "myuser2" will be permanently removed.
    Are you sure? (y/n) y
    DROP ROLE myuser2;