Summary of Postgres operations in Ubuntu

Summary of Postgres operations in Ubuntu

1.Basic operation commands

• After the installation is complete, PostgreSQL creates a database user account named Postgres by default. It is a super Administrator account like the root account of MySQL and the SA account of sqlserver, but unlike MySQL, postgreSQL also created a Unix account named S.

• Like the master database of sqlserver, the default database of PostgreSQL is template1. You can use the command line management tool Psql to manage it, provided that you switch to the Postgres SYSTEM account.

• The common command line commands are as follows:

Createdb Dbname: Create a database
Dropdb Dbname: Delete a database
Createuser [-- superuser] Username: Create a user
Dropuser Username: Delete a user

• Commonly used commands in interactive environments:

\ Du: list all current user information
\ L: list all current database information
\ Q: Exit
\ Password Username: Modify the password of the specified user
\ DN: View Shema
\ I: The command reads the command from the specified file

2.Use shell command line to add users and create databases

PostgreSQL provides the command line programs createuser and createdb. You can use the shell command line to add users and create databases. Take dbuser and exampledb as examples.

• First, create a database user dbuser and specify it as a Super User.
  Sudo-u Postgres createuser -- superuser dbuser

• Then, log on to the Database Console, set the password of the dbuser user, and then exit the console.
  Sudo-u Postgres Psql
  \ Password dbuser
  \ Q

• Next, create the database exampledb in the shell command line and specify the owner as dbuser.
  Sudo-u Postgres createdb-O dbuser exampledbshell command line to log on to the database

• The user and database in the preceding example are used as an example to log on to the database in the name of a new user.
  Psql-u dbuser-D exampledb-H 127.0.0.1-P 5432
  The parameter Meanings of the preceding command are as follows:-u specifies the user,-D specifies the database,-H specifies the server, and-P specifies the port. After entering the preceding command, the system will prompt you to enter the password of dbuser. Enter the correct information to log on to the console.
  
  

3. Use the following command to enable or disable PostgreSQL services in Ubuntu:

• Usage:/etc/init. d/PostgreSQL {START | stop | restart | reload | force-Reload | status} [version ..]
  
  

4. Solutions to some authentication problems during database connection:

• If the following error occurs:
  
  Psql: Fatal: IDENT authentication failed for user "mypguser"
  
  Edit your pg_cmd.conf file./etc/postgresql/X.Y/main/pg_hba.conf, X.y is the version number of your PostgreSQL, and the peer in the following line is changed to trust:

local   all    all    trust     # replace ident or peer with trust

 

• If the following error occurs:
  
  Psql: Fatal: Peer authentication failed foruser "mypguser"
  
  Please still modify the pg_cmd.conf file. The peer in the following line is MD5:

local   all    all    md5       # replace peer with md5

After completing the preceding modification, reload PostgreSQL:

/etc/init.d/postgresql reload

This article is from the "strabismus ceiling" blog, please be sure to keep this source http://lemidi.blog.51cto.com/8601832/1535349