PostgreSQL installation and simple use 1th/2 page _postgresql

Source: Internet
Author: User
Tags postgresql psql create database
According to my understanding of the four domestic databases, three of which are based on PostgreSQL development. And, because of the flexibility of the license, anyone can use, modify, and distribute PostgreSQL for any purpose, whether for private, commercial, or academic research. This article simply introduces the installation and simple use of PostgreSQL, the grammatical aspects involved in relatively few, to facilitate the novice on the road for the purpose.

1. System environment and installation method;
PostgreSQL installation method is more flexible, you can use the source package installation, you can use the distribution of the software package to install, you can also use online installation ...

1.1 System Environment: Ubuntu Linux 7.04; fedora;slackware;

1.2 Installation;
Install under Ubuntu
Software is actually very simple, with the new software package Manager search Psql will be able to find postgresql-client-8.2 (at the same time can be found in the 8.1 version, which can be), selected-application can be. or enter at the end of the terminal
xiaop@localhost$ sudo apt-get install postgresql-8.2
Install under Slackware:
Please go to the to find the corresponding version of your system, install it with Pkginstall, or you can install the Slap-get tool, install it online automatically, and use the root permission to general sudo. Reference to Su and sudo; "control of super rights in Linux systems"
Install the PostgreSQL package, use the following method;
xiaop@localhost# Pkginstall post*.tgz
xiaop@localhost# Slapt-get--install postgresql-8.2.4
In Fedora, you can use the Package Online installation tool to install
Note: This installs the PostgreSQL 8.2, which automatically creates a default database cluster (translation in "main" and generates a database Superuser postgres.

2. Start PostgreSQL database server;

2.1 Startup method in the popular Linux distribution;
In the Ubuntu system, the server startup script is placed in the/ETC/INIT.D directory, you can start it in the following way, which is similar to Fedora and Gentoo;
xiaop@localhost~#/etc/init.d/postgresql-8.2 start Note: start;
xiaop@localhost~#/etc/init.d/postgresql-8.2 restart Note: reboot;
xiaop@localhost~#/etc/init.d/postgresql-8.2 stop Note: stopping;
xiaop@localhost~#/etc/init.d/postgresql-8.2 Status Note: View status;
In Slackware, PostgreSQL's startup scripts are placed in the/ETC/RC.D directory if you use packages downloaded from or online packages installed;
xiaop@localhost~#/etc/rc.d/rc.postgres Start
If you compile the installation with the source package, start PostgreSQL, please check the PostgreSQL official document;

2.2 About PostgreSQL startup and storage directory;
When the PostgreSQL server is started, it is typically started with a postgres user, except for the compiled installation; the storage of the database is typically in a related directory in/var/lib, such as/var/lib/pgsql or/var/lib/postgresql/ 8.2/main/directories, and so on; different distributions may not be the same, but they are the same, and you can place the database elsewhere by modifying the location of the data store;

3. Create User

Add user command format.
CreateUser is the encapsulation of SQL command CREATE user.
Command: CreateUser [-A] [-a] [-d] [-d] [E] [P] [-H host name] [-P port] User name

Parameter description:
[-A]: Allows other users to be created, equivalent to creating a super user;
[-A]: Do not allow this user to create other users;
[-D]: Allows this user to create a database;
[-d]: This user is not allowed to create a database;
[-E]: Displays the execution process on the shell;
[-P]: When creating a user, set the password at the same time;
[-H Host name]: Create a user for a postgres on a host;
[-P port]: use with the-h parameter to specify the port of the host.

3.1 Add users;

3.1.1 Create user with no parameters;
xiaop@localhost~$ CreateUser TestUser
Shall The new user is allowed to create databases? (y/n) n--------Whether a database can be created: no
Shall The new user is allowed to create more new users? (y/n) n---------whether a new user can be created: no
Note: When creating a user without parameters, Postgres asks the user for permission, and the example above creates an ordinary user;

3.1.2 Creates a user for the specified host and port;
xiaop@localhost~$ createuser-h 5000-d-a-e testuser
CREATE USER Joe Nocreatedb Nocreateuser;
Note: This command creates a user testuser for the 5000 port of the host, and this user cannot create the database and other users.

3.1.3 To create a super user;
xiaop@localhost~$ createuser-p-d-a-e testuser
Enter Password for new User:testuser
Enter it Again:testuser
CREATE USER Joe PASSWORD ' TestUser ' createdb createuser;
Note: This command creates a local superuser (-a), creates a database (-D), and requires a password to be set.

3.2 Delete User:
Command: Dropuser [-i] [-h] [-p] [E] User name
Parameter description:
[-i]: Before deleting a user, ask for confirmation;
[-H host name]: Deletes a postgres user on a host;
[-P port]: Used with the-h parameter to specify the port of the host;
[-E]: Displays the execution process on the shell.

3.2.1 Deletes the local postgres user;
xiaop@localhost~$ Dropuser TestUser

3.2.2 Deletes a user on a remote postgres server;
xiaop@localhost~$ dropuser-p 5000-h testuser
The User "TestUser" and any owned databases would be permanently deleted.
Are you sure? (y/n) Y
DROP USER "TestUser"
Note: This command deletes the user testuser of the host (-h) of Port 5000 (-p) and requires confirmation (-i);

4. Create and delete databases;

4.1 Creating a Database
The first example of how you can access a database server is to try to create a database;
To create a new database, called MyDB in our example, you can use the following command:
xiaop@localhost~$ Createdb MyDB
It should generate the following response:
If so, this step succeeds if you see information similar to the following
Createdb:command not found
Then it is PostgreSQL not installed, or is not loaded at all;
You can also create a database with a different name. PostgreSQL allows you to create any number of databases on a single node. The database name must start with a letter and is less than 63 characters in length. A convenient approach is to create a database with the same name as your current username. Many tools assume that the database name is the default database name, so you can save your keystrokes. To create such a database, you only need to type:
xiaop@localhost~$ Createdb

4.2 Deleting a database
If you don't want to use your database anymore, you can delete it. For example, if you are the owner (creator) of a database mydb, you can delete it with the following command:
xiaop@localhost~$ dropdb MyDB
Note: (For this command, the database name is not the default user name.) So you have to declare it. This action physically deletes all files associated with the database and cannot be canceled, so be sure to think before you do this.

5. Access to the database
Once you have created the database, you can access it, and you can run the PostgreSQL Interactive terminal program called Psql, which allows you to interactively enter, edit, and execute SQL commands. (For graphical logins, see 6.) PostgreSQL Graphical management tool pgAdmin3)

5.1 Activating the database
You need to start psql and test the example just now. You can activate it for the MyDB database with the following command:
xiaop@localhost~$ Psql MyDB
If you omit the name of the database, it defaults to your user account name.
Welcome to Psql 8.2.4, the PostgreSQL Interactive terminal.
Type: \copyright for distribution Terms
\h for help with SQL commands
\? For help with Psql commands
\g or terminate with semicolon to execute query
\q to quit
Note: The last line mydb=#, this prompt means that you are a database superuser.

5.2 Help and Exit database
The PSQL program has some internal commands that are not part of the SQL command. They start with a backslash, "". Some of these commands are listed in the Welcome message. For example, you can use the following command to get help syntax for various PostgreSQL SQL commands:
Mydb=> \h
To exit Psql, type
Mydb=> \q
Then Psql quits and returns you to the command line shell; (For more information about internal commands, you can type \? At the psql prompt.) )
Current 1/2 page 12 Next read the full text
Related Article

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: 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.