PostgreSQL installation and simple use

PostgreSQL is one of the most popular databases. This database research plan originated from Berkeley (BSD) has been developed into an international development project and has a wide range of users. According to my knowledge, three of the four domestic databases are developed based on PostgreSQL. Moreover, because of the flexible license, anyone can use, modify, and distribute PostgreSQL for free for any purpose, whether for private use, commercial use, or academic research. This article only briefly introduces the installation and simple use of PostgreSQL. The syntax involves a little, so you can get started on the road.

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 PostgreSQL interactive terminal.ProgramPsql 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 \?.)

6. PostgreSQL graphical management tool pgadmin3;

Version: version1.4.3

6.1 installation;

6.1.1 install ubuntu;

There are two methods:

1. Search pgadmin3 in the new software package manager and find pgadmin3-Application

2. Enter the following command on the terminal:
Xiaop @ xiaop-LAPTOP :~ $ Sudo apt-Get install pgadmin3

6.1.2 installation of other systems;

You can refer to the installation methods of normal software in other systems. This document is similar and will not be described here;

6.2 simple use of pgadmin3;

The graphical management system is relatively intuitive. You can perform operations under the command line and view the effect under pgadmin3;

6.2.1 start pgadmin3

You can find the startup Item of pgadmin3 in application --- system tools;
You can also enter:
Xiaop @ xiaop-LAPTOP :~ $/Usr/bin/pgadmin3 start

6.2.2 connect to the created database mydb;

Click file ----- Add Server, and then enter:
Address: localhost
Description: server name)
Database Maintenance: ipvs
User name: Create one by yourself (for details, see create a user)
Password: corresponds to the user name (created by yourself when you create a user)

Click "OK" to view the postsql database;
Note: The databases in pgadmin3 and the databases created in the terminal are completely synchronized (you can use refresh to view the results). pgadmin3 is a convenient graphical management tool that allows you to create charts, database management. For details about pgadmin3, we will discuss it later. This article mainly introduces the operations under the command line. The graphical management tool can achieve all the command lines. You can create a table under the command line and check whether the table is synchronized on pgadmin3:

7. Create and Delete tables;

7.1 create a new table;

After creating a database, you can create a new table. You can create a table by declaring the table name and the names and types of all fields. For example:
Mydb # create table weather (
City varchar (80 ),
Temp_lo int, -- Minimum Temperature
Temp_hi int, -- maximum temperature
PRCP real, -- Precipitation
Date
);

Note: you can type these items together with line breaks in Psql. Psql can identify this command until the end of the semicolon, do not forget ";"
You can use blank spaces (that is, spaces, tabs, and line breaks) in SQL commands ). This means that you can use the Alignment Method above to type the command. Two dashes ("--") Introduce comments. Anything following it is ignored until the end of the line. SQL is a language that is case-insensitive to keywords and identifiers. It can retain the case-insensitive attributes of identifiers only when they are surrounded by double quotation marks.

7.2 data type;

In the preceding example, varchar (80) declares a data type that can store any string up to 80 characters. Int is a common integer. Real is a type used to store Single-precision floating point numbers. The date type can be customized.
Postgressql supports standard SQL types such as int, smallint, real, double precision, char (N), varchar (N), date, time, timestamp, and interval, other common types and a wide range of geometric types are also supported. PostgreSQL can be customized to any user-defined data type. You can refer to the Chinese documentation of postgresql for query;

7.3 delete a table;

If you no longer need a table or want to create a different table, you can use the following command to delete it:
Mydb # Drop table tablename

8. Add rows to the table;

8.1 insert;

Insert is used to Add rows to the table. You can enter (operate in the database ):
Mydb # insert into weather values ('san Francisco ', 46, 50, 0.25, '2017-11-27 ');

Note: All data types Use a fairly clear input format. Constants that are not simple numeric values must be enclosed by single quotation marks ('), just as in the example.

8.2 point type input;

The point type requires a coordinate pair as the input, as follows:
Mydb # insert into cities values ('san Francisco ',' (-194.0, 53.0 )');

8.3 copy;

You can also use copy to load large amounts of data from text files. This is usually faster, because the Copy command is optimized for such applications, but less flexible than insert. For example:
Mydb # copy weather from '/home/user/weather.txt ';

Note: weather.txt is a format-compliant table content document that you have written in advance;

9. query a table;

9.1 select;

To retrieve data from a table is to query this table. SQL select is used for this purpose. This statement is divided into the selection list (list the fields to be returned), the table list (list the parts of the table from which data is retrieved), and optional conditions (declare any restrictions ). For example, to retrieve all weather rows, type:
Select * from weather;
<Code>
Output result:
<Code>
City | temp_lo | temp_hi | PRCP | date
--------------- + --------- + ------ + ------------
San Francisco | 46 | 50 | 0.25 |
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | 1994-11-29
(3 rows)

You can write any expression in the selection list, not just the field list. For example, you can:
Select city, (temp_hi + temp_lo)/2 as temp_avg, date from weather;

We can conclude that:
City | temp_avg | date
--------------- + ---------- + ------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)

Note how the as Clause renames the output field. (The as clause is optional .)

9.2 Where;

You can use the WHERE clause to modify the rows required for a query. The where clause contains a Boolean (True Value) expression, which is returned only when the Boolean expression is a real row. You can use common boolean operators (and, or, and not) in conditions ). For example, the following query retrieves the weather in the rainy days of San Francisco:
Mydb # select * from weather
Where city = 'san Francisco 'and PRCP> 0.0;

Result:
City | temp_lo | temp_hi | PRCP | date
--------------- + --------- + ------ + ------------
San Francisco | 46 | 50 | 0.25 |
(1 row)

9.3 sorting;

You can request that the returned query be sorted:
Mydb # select * from weather
Order by city;

Result:
City | temp_lo | temp_hi | PRCP | date
--------------- + --------- + ------ + ------------
Hayward | 37 | 54 | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 |

In this example, the sorting order is not absolutely clear, so you may see the random sorting of San Francisco rows. However, if you use the following statement, the above results will always be obtained.
Select * from weather
Order by city, temp_lo;

You can sort the query results in a certain order and eliminate repeated row outputs:
Mydb # select distinct City
From weather;

Result:
City
---------------
Hayward
San Francisco
(2 rows)

Again, the order of result rows may be random.

10. view;

Assume that your application is particularly interested in the combination list of weather records and city locations, and you do not want to type these queries each time. You can create a view on this query, which gives the query a name and you can reference it like a normal table.

After entering the database, enter:
Mydb # create view myview
Select city, temp_lo, temp_hi, PRCP, date, location
From weather, cities
Where city = Name;

Create a view;

Then select the created View:
Select * From myview;

The result is as follows:
City | temp_lo | temp_hi | PRCP | date | location
--------------- + --------- + ------ + ------------ + -----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53)
(2 rows)

11. Update rows;

You can use the update command to update existing rows. If you find that the number of thermometers in November 28 is twice lower, you can update the data in the following way:
Mydb # update weather
Set temp_hi = temp_hi-2, temp_lo = temp_lo-2
Where date> '2017-11-28 ';

Check the new data status:
Select * from weather;

Result:
City | temp_lo | temp_hi | PRCP | date
--------------- + --------- + ------ + ------------
San Francisco | 46 | 50 | 0.25 |
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | 1994-11-29
(3 rows)

12. Delete rows;

You can use the DELETE command to delete data rows from a table. If you are no longer interested in the weather of Hayward, you can use the following method to delete the rows from the table:
Mydb # Delete from weather where city = 'hayward ';

Be careful when using the following statements
Delete from tablename;

If no conditions exist, delete deletes all rows from the specified table and clears them. The system will not ask you for confirmation before doing this!

13. About this article

About database backup and recovery, we will introduce it in PostgreSQL backup and recovery. Most of the information in this article is based on the Chinese documents, so that it is easier for the brothers to find out, detailed information is available in Chinese documents. Thank you for your advice :)