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 linuxpackages.net 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
Or
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 pgsqldb.org) "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 linuxpackages.net 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
CREATE USER
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 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 the host 172.28.18.51, 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;
CREATE USER
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
DROP USER
3.2.2 Deletes a user on a remote postgres server;
xiaop@localhost~$ dropuser-p 5000-h 172.28.18.51-i-e testuser
The User "TestUser" and any owned databases would be permanently deleted.
Are you sure? (y/n) Y
DROP USER "TestUser"
DROP USER
Note: This command deletes the user testuser of the host 172.28.18.51 (-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:
CREATE DATABASE
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
mydb=#
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
