PostgreSQL installation and simple use page 1/2

According to my knowledge, three of the four domestic databases are developed based on PostgreSQL. In addition, because of the flexible license, anyone can use, modify, and distribute PostgreSQL for free for any purpose, whether it is private, commercial, or academic research. This article only briefly introduces the installation and simple use of postgresql, and involves a small number of syntaxes to facilitate new users.

1. System Environment and installation method;
The Installation Method of PostgreSQL is flexible. you can install it using the source code package, the software package that comes with the release version you are using, and online installation ......

1.1 system environment: Ubuntu Linux 7.04; Fedora; Slackware;

1.2 installation;
Install in Ubuntu
The software is actually very simple, with the new software package manager search psql can find the postgresql-client-8.2 (at the same time you can find version 8.1, which can be), select-application. Or enter
Xiaop @ localhost $ sudo apt-get install postgresql-8.2
Slackware installation:
Go to linuxpackages.net to find the corresponding version of your system, use pkginstall to install it, or install the slap-get tool online for automatic installation. To use the root permission, you can use general sudo. References for su and sudo; control of super permissions in Linux
The following method can be used to install the postgresql software package;
Xiaop @ localhost # pkginstall post *. tgz
Or
Xiaop @ localhost # slapt-get -- install postgresql-8.2.4
In Fedora, you can use the package online Installation tool to install
Note: After PostgreSQL 8.2 is installed, a default database cluster (translated in pgsqldb.org) "main" is automatically created and a database superuser postgres is generated.

2. Start the PostgreSQL database server;

2.1 How to start popular Linux distributions;
In Ubuntu, the server startup script is placed in the/etc/init. d directory. You can use the following method to start it, which is similar to that of Fedora and Gentoo;
Xiaop @ localhost ~ #/Etc/init. d/postgresql-8.2 start Note: start;
Xiaop @ localhost ~ #/Etc/init. d/postgresql-8.2 restart Note: restart;
Xiaop @ localhost ~ #/Etc/init. d/postgresql-8.2 stop Note: stop;
Xiaop @ localhost ~ #/Etc/init. d/postgresql-8.2 status note: view the status;
In Slackware, the PostgreSQL STARTUP script is placed in the/etc/rc. d directory. If you use a software package downloaded from linuxpackages.net or a software package installed online;
Xiaop @ localhost ~ #/Etc/rc. d/rc. S start
If you use the source code package for compilation and installation and start PostgreSQL, please refer to the official PostgreSQL documentation;

2.2 about PostgreSQL startup and storage directory;
When the PostgreSQL server is started, it is generally started by the postgres user, except for Self-compilation and installation. The database storage is generally stored in the relevant directory in/var/lib, for example,/var/lib/pgsql or/var/lib/postgresql/8.2/main/directory. Different Release versions may not be the same, but they are similar, you can modify the data storage location to store the database elsewhere;

3. Create a user

Add the USER command format.
Createuser is the encapsulation of the SQL command CREATE USER.
Command: createuser [-a] [-A] [-d] [-D] [-e] [-P] [-h host name] [-p port] User Name

Parameter description:
[-A]: allows the creation of other users, which is equivalent to creating a Super User;
[-A]: This user is not allowed 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 to the Shell;
[-P]: set a password when creating a user;
[-H host name]: Creates a user for ipvs on a host;
[-P port]: used with the-h parameter to specify the host port.

3.1 Add a user;

3.1.1 create users without parameters;
Xiaop @ localhost ~ $ Createuser testuser
Shall the new user be allowed to create databases? (Y/n) n -------- can I create a database?: No
Shall the new user be allowed to create more new users? (Y/n) n --------- can I create a new user? No
CREATE USER
Note: when a user is created without parameters, S will ask the user's permissions. In the preceding example, a common user is created;

3.1.2 create a user for the specified host and port;
Xiaop @ localhost ~ $ Createuser-h 172.28.18.51-p 5000-D-A-e testuser
Create user joe nocreatedb nocreateuser;
CREATE USER
Note: This command creates user testuser for port 5000 of host 172.28.18.51. This user cannot create databases or other users.

3.1.3 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;
CREATE USER
Note: This command creates a Super User (-a) locally, creates a database (-d), and requires a password.

3.2 delete a user:
Command: dropuser [-I] [-h] [-p] [-e] User Name
Parameter description:
[-I]: confirmation is required before deleting a user;
[-H host name]: deletes the ipvs user on a host;
[-P port]: used with the-h parameter to specify the host port;
[-E]: displays the execution process to the Shell.

3.2.1 Delete A local Postgres user;
Xiaop @ localhost ~ $ Dropuser testuser
DROP USER

3.2.2 delete users on the remote ipvs server;
Xiaop @ localhost ~ $ Dropuser-p 5000-h 172.28.18.51-I-e testuser
User "testuser" and any owned databases will be permanently deleted.
Are you sure? (Y/n) y
Drop user "testuser"
DROP USER
Note: This command deletes the user testuser of port 5000 (-p) of the host 172.28.18.51 (-h) and requires confirmation (-I );

4. Create and delete databases;

4.1 create a database
The first example of whether you can access the database server is to create a database;
To create a new database, in our example, mydb, you can use the following command:
Xiaop @ localhost ~ $ Createdb mydb
It should generate the following response:
CREATE DATABASE
If so, this step is successful. If you see information similar to the following:
Createdb: command not found
That is, PostgreSQL is not installed properly, or it is not installed at all;
You can also create a database with another name. PostgreSQL allows you to create any number of databases on a node. The database name must start with a letter and be less than 63 characters long. A convenient way is to create a database with the same name as your current user name. Many tools assume that the database name is the default database name, which can save your key. To create a database like this, just type:
Xiaop @ localhost ~ $ Createdb

4.2 Delete A Database
If you no longer want to use your database, you can delete it. For example, if you are the database mydb owner (creator), you can use the following command to delete it:
Xiaop @ localhost ~ $ Dropdb mydb
Note: (for this command, the database name is not the default user name. In this way, you must declare it .) This action physically deletes all files related to the database and cannot be canceled. Therefore, you must be clear before doing this;

5. Access the database
Once you create a database, you can access it. You can run the terminal program of PostgreSQL interaction, called psql, which allows you to enter, edit, and execute SQL commands interactively. (For graphical login, see 6. Postgresql graphical management tool pgAdmin3)

5.1 activate a database
You need to start psql to test the example just now. You can use the following command to activate the mydb database:
Xiaop @ localhost ~ $ Psql mydb
If you omit the database name, it is your user account name by default.
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
Mydb = #
Note: The last line is mydb = #. This prompt indicates that you are a database superuser.

5.2 help and exit the database
Psql contains some internal commands that do not belong to SQL commands. They start with a backslash ,"". Some of these commands are listed in the welcome information. For example, you can use the following command to obtain the help Syntax of various PostgreSQL SQL commands:
Mydb => \ h
To exit psql, Type
Mydb => \ q
Psql then exits and returns the shell command line to you. (To obtain more information about internal commands, Type \? At the psql prompt \?.)