As far as I know, the four major domestic databases in China, three of which are developed based on PostgreSQL. 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 purposes. This article just briefly introduces the installation and simple use of postgresql. There is little involved in syntax, and it is for the purpose of making it easier for novices to get on the road.
1. System environment and installation method;
The installation method of PostgreSQL is relatively flexible. It can be installed with source code packages, or the software packages brought by the distribution you use, and it can also be installed online...
1.1 System environment: Ubuntu Linux 7.04; Fedora; Slackware;
1.2 Installation;
Install under Ubuntu
The software is actually very simple. Use Xinlide software package manager to search for psql and you can find postgresql-client-8.2 (you can also search for version 8.1, and either one is OK), and just select -app. Or enter it in the terminal
xiaop@localhost$ sudo apt-get install postgresql-8.2
Install under Slackware:
Please go to find the corresponding version of the system you are using, use pkginstall to install, or you install the slap-get tool and automatically install it online; to use root permissions, you can use sudo. References about su and sudo; "Control of Super Permissions in Linux Systems"
To install the postgresql software package, you can use the following methods;
xiaop@localhost# pkginstall post*.tgz
or
xiaop@localhost# slapt-get --install postgresql-8.2.4
In Fedora, you can install it using the package online installation tool
Note: This way PostgreSQL 8.2 is installed. This installation will automatically create a default database cluster (translation method in the form) "main" and generate a database superuser postgres.
2. Start the PostgreSQL database server;
2.1 Starting methods in popular Linux distributions;
In Ubuntu system, the server startup script is placed in the /etc/ directory. You can use the following method to start it, which is similar to Fedora and Gentoo;
xiaop@localhost~# /etc//postgresql-8.2 start Note: Start;
xiaop@localhost~# /etc//postgresql-8.2 restart Note: Restart;
xiaop@localhost~# /etc//postgresql-8.2 stop Note: Stop;
xiaop@localhost~# /etc//postgresql-8.2 status Note: View status;
In Slackware, PostgreSQL startup scripts are placed in the /etc/ directory if you use a package downloaded from or a package installed online;
xiaop@localhost~# /etc// start
If you use the source code package to compile and install and start PostgreSQL, please check the official PostgreSQL documentation;
2.2 About PostgreSQL startup and storage directories;
When starting the PostgreSQL server, it is usually started with the postgres user, except for self-compilation and installation; the database storage is generally stored in /var/lib related directories, such as /var/lib/pgsql or /var/lib/postgresql/8.2/main/ directory, etc.; different distributions may be different, but they are still similar. You can store the database in other places by modifying the data storage location;
3. Create a user
Add user command format.
createuser is an encapsulation of the SQL command CREATE USER.
Command: createuser [-a] [-A] [-d] [-D] [-e] [-P] [-h Hostname] [-p port] Username
Parameter description:
[-a]: Allows creation of other users, which is equivalent to creating a super user;
[-A]: This user is not allowed to create other users;
[-d]: Allow this user to create a database;
[-D]: This user is not allowed to create a database;
[-e]: Display the execution process on the shell;
[-P]: Set password at the same time when creating a user;
[-h Hostname]: Create a user for Postgres on a host;
[-p port]: Used with the -h parameter to specify the port of the host.
3.1 Add users;
3.1.1 Create user without parameters;
xiaop@localhost~$ createuser testuser
Shall the new user be allowed to create databases? (y/n) n --------------------------------------------------------------------------------------------------------------
Shall the new user be allowed to create more new users? (y/n) n ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE USER
Note: When creating a user without parameters, Postgres will ask for permissions of this user. The above example creates a normal user;
3.1.2 Create users on 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 a user testuser for the 5000 port of host 172.28.18.51. This user cannot create databases and 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 superuser (-a) locally, which can create a database (-d) and requires a password to be set.
3.2 Delete the user:
Command: dropuser [-i] [-h] [-p] [-e] Username
Parameter description:
[ -i]: Request confirmation before deleting the user;
[-h Hostname]: Delete the Postgres user on a certain host;
[-p port]: Used with the -h parameter to specify the port of the host;
[-e]: Display the execution process on the shell.
3.2.1 Delete the local Postgres user;
xiaop@localhost~$ dropuser testuser
DROP USER
3.2.2 Delete users on the remote Postgres 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 host 172.28.18.51 (-h), and needs to confirm (-i);
4. Create and delete databases;
4.1 Create a database
The first example of seeing if you can access a database server is trying to create a database;
To create a new database, in our example, called mydb, you can use the following command:
xiaop@localhost~$ createdb mydb
It should generate a response like this:
CREATE DATABASE
If this is the case, then this step will succeed, if you see information like the following
createdb: command not found
Then PostgreSQL is not installed properly, or it is not installed at all;
You can also create a database with other names. PostgreSQL allows you to create any number of databases on a single node. The database name must start with a letter and be less than 63 characters long. A convenient way to do this 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 this saves you keystrokes. To create such a database, just type:
xiaop@localhost~$ createdb
4.2 Delete the database
If you never want to use your database again, you can delete it. For example, if you are the owner (creator) of the 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 username. This way you must declare it.) This action physically deletes all files related to the database and is not cancelable, so you must think clearly before doing this;
5. Access the database
Once you create the database, you have access to it, and you can run a terminal program for PostgreSQL interaction called psql, which allows you to enter, edit, and execute SQL commands interactively. (For graphical login, please refer to 6. Postgresql graphical management tool pgAdmin3)
5.1 Activate the database
You need to start psql and experiment with the example just now. You can activate it for the mydb database using the following command:
xiaop@localhost~$ psql mydb
If you omit the database name, it is the default name of your user account.
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 mydb=#, this prompt means you are a database superuser.
5.2 Help and exit the database
The psql program has some internal commands that do not belong to SQL commands. They begin with a backslash, "". Some of these commands are listed in the welcome message. 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 will then exit and return you to the command line shell; (To get more information about internal commands, you can type \? at the psql prompt.)
1. System environment and installation method;
The installation method of PostgreSQL is relatively flexible. It can be installed with source code packages, or the software packages brought by the distribution you use, and it can also be installed online...
1.1 System environment: Ubuntu Linux 7.04; Fedora; Slackware;
1.2 Installation;
Install under Ubuntu
The software is actually very simple. Use Xinlide software package manager to search for psql and you can find postgresql-client-8.2 (you can also search for version 8.1, and either one is OK), and just select -app. Or enter it in the terminal
xiaop@localhost$ sudo apt-get install postgresql-8.2
Install under Slackware:
Please go to find the corresponding version of the system you are using, use pkginstall to install, or you install the slap-get tool and automatically install it online; to use root permissions, you can use sudo. References about su and sudo; "Control of Super Permissions in Linux Systems"
To install the postgresql software package, you can use the following methods;
xiaop@localhost# pkginstall post*.tgz
or
xiaop@localhost# slapt-get --install postgresql-8.2.4
In Fedora, you can install it using the package online installation tool
Note: This way PostgreSQL 8.2 is installed. This installation will automatically create a default database cluster (translation method in the form) "main" and generate a database superuser postgres.
2. Start the PostgreSQL database server;
2.1 Starting methods in popular Linux distributions;
In Ubuntu system, the server startup script is placed in the /etc/ directory. You can use the following method to start it, which is similar to Fedora and Gentoo;
xiaop@localhost~# /etc//postgresql-8.2 start Note: Start;
xiaop@localhost~# /etc//postgresql-8.2 restart Note: Restart;
xiaop@localhost~# /etc//postgresql-8.2 stop Note: Stop;
xiaop@localhost~# /etc//postgresql-8.2 status Note: View status;
In Slackware, PostgreSQL startup scripts are placed in the /etc/ directory if you use a package downloaded from or a package installed online;
xiaop@localhost~# /etc// start
If you use the source code package to compile and install and start PostgreSQL, please check the official PostgreSQL documentation;
2.2 About PostgreSQL startup and storage directories;
When starting the PostgreSQL server, it is usually started with the postgres user, except for self-compilation and installation; the database storage is generally stored in /var/lib related directories, such as /var/lib/pgsql or /var/lib/postgresql/8.2/main/ directory, etc.; different distributions may be different, but they are still similar. You can store the database in other places by modifying the data storage location;
3. Create a user
Add user command format.
createuser is an encapsulation of the SQL command CREATE USER.
Command: createuser [-a] [-A] [-d] [-D] [-e] [-P] [-h Hostname] [-p port] Username
Parameter description:
[-a]: Allows creation of other users, which is equivalent to creating a super user;
[-A]: This user is not allowed to create other users;
[-d]: Allow this user to create a database;
[-D]: This user is not allowed to create a database;
[-e]: Display the execution process on the shell;
[-P]: Set password at the same time when creating a user;
[-h Hostname]: Create a user for Postgres on a host;
[-p port]: Used with the -h parameter to specify the port of the host.
3.1 Add users;
3.1.1 Create user without parameters;
xiaop@localhost~$ createuser testuser
Shall the new user be allowed to create databases? (y/n) n --------------------------------------------------------------------------------------------------------------
Shall the new user be allowed to create more new users? (y/n) n ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE USER
Note: When creating a user without parameters, Postgres will ask for permissions of this user. The above example creates a normal user;
3.1.2 Create users on 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 a user testuser for the 5000 port of host 172.28.18.51. This user cannot create databases and 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 superuser (-a) locally, which can create a database (-d) and requires a password to be set.
3.2 Delete the user:
Command: dropuser [-i] [-h] [-p] [-e] Username
Parameter description:
[ -i]: Request confirmation before deleting the user;
[-h Hostname]: Delete the Postgres user on a certain host;
[-p port]: Used with the -h parameter to specify the port of the host;
[-e]: Display the execution process on the shell.
3.2.1 Delete the local Postgres user;
xiaop@localhost~$ dropuser testuser
DROP USER
3.2.2 Delete users on the remote Postgres 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 host 172.28.18.51 (-h), and needs to confirm (-i);
4. Create and delete databases;
4.1 Create a database
The first example of seeing if you can access a database server is trying to create a database;
To create a new database, in our example, called mydb, you can use the following command:
xiaop@localhost~$ createdb mydb
It should generate a response like this:
CREATE DATABASE
If this is the case, then this step will succeed, if you see information like the following
createdb: command not found
Then PostgreSQL is not installed properly, or it is not installed at all;
You can also create a database with other names. PostgreSQL allows you to create any number of databases on a single node. The database name must start with a letter and be less than 63 characters long. A convenient way to do this 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 this saves you keystrokes. To create such a database, just type:
xiaop@localhost~$ createdb
4.2 Delete the database
If you never want to use your database again, you can delete it. For example, if you are the owner (creator) of the 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 username. This way you must declare it.) This action physically deletes all files related to the database and is not cancelable, so you must think clearly before doing this;
5. Access the database
Once you create the database, you have access to it, and you can run a terminal program for PostgreSQL interaction called psql, which allows you to enter, edit, and execute SQL commands interactively. (For graphical login, please refer to 6. Postgresql graphical management tool pgAdmin3)
5.1 Activate the database
You need to start psql and experiment with the example just now. You can activate it for the mydb database using the following command:
xiaop@localhost~$ psql mydb
If you omit the database name, it is the default name of your user account.
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 mydb=#, this prompt means you are a database superuser.
5.2 Help and exit the database
The psql program has some internal commands that do not belong to SQL commands. They begin with a backslash, "". Some of these commands are listed in the welcome message. 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 will then exit and return you to the command line shell; (To get more information about internal commands, you can type \? at the psql prompt.)
12Next pageRead the full text