Summary of Postgres operations in Ubuntu

Source: Internet
Author: User
Tags psql postgres createdb

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.