PostgreSQL (i) tutorial-----from the beginning

First, installation

Naturally, before you can start using PostgreSQL, you must install it. PostgreSQL is probably already installed on your node because it may be included in the release of your operating system, or the system administrator has installed it. If so, you should get information about how to access PostgreSQL from the documentation for your operating system or from your system administrator .

If you're not sure if PostgreSQL is installed, or if you don't know if you can do your own experiment with it (already installed), you can install it yourself. It's not difficult, and it's a good practice. PostgreSQL can be installed by any non-privileged user and does not require superuser (root) privileges.

II. structure basis

Before we go on, you should understand the system architecture of PostgreSQL first. Understanding how the interaction between parts of PostgreSQL will make this section easier to understand.

In database terminology,PostgreSQL uses a client/server model. A PostgreSQL session consists of the following related processes (Programs):

• A server process that manages database files, accepts joins from the client application to the database, and performs operations on the database on behalf of the client. The database server program is called Postgres.

• Client (front-end) applications for users who need to perform database operations. The client application may be a variety of itself: it can be a text-oriented tool or a graphical interface, or a Web server that displays Web pages by accessing a database, or a specially crafted database management tool. Some client applications are provided with the PostgreSQL release, but most are user-developed.

As with typical client/server applications (c/S applications), these clients and servers can be on different hosts. At this point they join the communication through the TCP/IP network. You should keep in mind that files that can be accessed on the client computer may not be accessible on the database server machine (or can only be accessed with a different file name).

The PostgreSQL server can handle multiple concurrent requests from the client. Therefore, it starts ("forks") a new process for each connection. From this point on, the client and the new server process are no longer communicating through the initial interference of the postgres process. Therefore, the primary server process is always running and waiting for the client to join, while the client and the associated server processes are up and down (of course, these are transparent to the user.) We introduce these primarily for the completeness of the content).

Third, create a database

Let's see if you can access the database server. The first example is trying to create a database. A running PostgreSQL server can manage many databases. Typically we use a single database for each project and for each user. Your site administrator may have created a database for you to use. If so, you can omit this step and skip to the next section.

To create a new database, in our example called mydb, you can use the following command:

Createdb MyDB

If you see a message similar to the following

Createdb:command not found

Then PostgreSQL is not installed properly. Either it is not installed at all, or your shell search path is not set correctly. Try calling the command with an absolute path:

/usr/local/pgsql/bin/createdb mydb    在你的站点上这个路径可能不一样。和你的站点管理员联系或者看看安装指导获取正确的位置。


另外一种响应可能是这样：

Createdb:could not connect to database Postgres:could not connect to server:no such file or directory is the        server Running locally and accepting        connections on Unix domain socket "/tmp/.s.pgsql.5432"?
This means that the server did not start or did not start as expected Createdb . Also, you should check the installation instructions or consult the administrator.

Another response might be this:

createdb:could not connect to database Postgres:FATAL:role ' Joe ' does not exist 
 here mentions your own login name. These behavior can occur if the administrator does not create postgresql user accounts for you. The  (postgresql user accounts and operating system user accounts are different. &NBSP 
  You need to become an operating system user who installs postgresql (usually  postgres ) to create a first user account. It is also possible to give you the postgresql username and your operating system user name is different; In this case, you need to use -u  option or use pguser  environment variable specifies your postgresql user name. 
 
 If you have a database user account but do not have the required permissions to create the database, you will see the following message: 
    

Createdb:database creation Failed:error:  Permission denied to create database
Not all users are licensed to create a new database. If PostgreSQL refuses to create a database for you, you need to have the site administrator give you permission to create the database. Please consult your site administrator when this happens. If you installed PostgreSQL yourself , then you should log in as the user who started the database server and then refer to the manual to complete the assignment of permissions. 

You can also create a database with a different name. PostgreSQL allows you to create any number of databases on a single site. The database name must start with a letter and be less than 63 characters in length. A handy way to do this 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, so you can save your keystrokes. To create such a database, you only need to type:

createdb

If you don't want to use your database anymore, you can delete it. For example, if you are the owner (creator) of the database mydb , you can delete it using the following command:

dropdb mydb

(For this command, the database name is not the default user name, so you must declare it). This action will physically delete all files associated with the database and cannot be canceled, so be sure to consider this before doing this.



Iv. Accessing the database
Once you have created a database, you can access it in the following ways:
(1) the interactive terminal program that runs PostgreSQL, which is called  psql, allows you to interactively enter, edit, and execute SQL commands. 
(2) Create and manage databases using an existing graphical front-end tool, such as pgadmin or an Office suite with ODBC or JDBC support.
(3) write a custom app using one of several binding languages.
You may need to start psql to experiment with the examples in this tutorial. You can activate it for the mydb database with the following command:
   Psql MyDB
If you do not provide a database name, then its default value is your user account name. You should have known this way in the previous section of using createdb .
In psql , you will see the following welcome message:

Psql (9.6.0) Type ' help ' for help.mydb=>

The last line may also be:

mydb=#

This prompt means that you are a database Superuser and most likely to appear when you have installed your own PostgreSQL instance. Being a superuser means that you are not restricted by access control. For the purposes of this tutorial, it doesn't matter if the superuser is super-user.

If you run into a problem when you start psql , go back to the previous section. The method of diagnosing Createdb is similar to the method of diagnosing psql , if the former can run then the latter should also be able to run.

The last line that psql prints is the prompt, which means that psql is listening to you, and this time you can type SQL queries into a psql maintained workspace. Try the following command:

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 the help syntax for the various PostgreSQL SQL commands:

mydb=>\h
To exit psql, enter:

mydb=>\q

Psql will exit and let you return to the command line shell. (To get more information about internal commands, you can type \?.) at the psql prompt. ）

PostgreSQL (i) tutorial-----from the beginning