Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to install and use postgresql". In daily operation, I believe many people have doubts about how to install and use postgresql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to install and use postgresql". Next, please follow the editor to study!

PostgreSQL is a powerful open source object-relational database system that uses and extends the SQL language and combines many capabilities to securely store and extend the most complex data workloads.

I. installation

First, install the PostgreSQL client.

Sudo apt-get install postgresql-client

Then, install the PostgreSQL server.

Sudo apt-get install postgresql

Normally, after the installation is complete, the PostgreSQL server will be automatically turned on on port 5432 of this machine.

If you also want to install the graphical management interface, you can run the following command, but this article does not cover this aspect.

Sudo apt-get install pgadmin3 II. Add new users and new databases

After the initial installation, a database named postgres and a database user named postgres are generated by default. It is important to note that a Linux system user named postgres is also generated.

Next, we use postgres users to generate other users and new databases. There are several ways to achieve this, and here are two.

The first method is to use the PostgreSQL console

First of all, create a new Linux user with the name you want, here is dbuser.

Sudo adduser dbuser

Then, switch to the postgres user.

Sudo su-postgres

Next, log in to the PostgreSQL console using the psql command.

Psql

At this time, it is equivalent to the system user postgres to log in to the database as a database user with the same name, which does not need to enter a password. If everything is all right, the system prompt will change to "postgres=#", indicating that you have entered the database console. The following commands are done in the console.

The first thing is to use the\ password command to set a password for the postgres user.

\ password postgres

The second thing is to create the database user dbuser (which you just created is the Linux system user) and set the password.

CREATE USER dbuser WITH PASSWORD 'password'

The third thing is to create the user database, in this case exampledb, and specify the owner as dbuser.

CREATE DATABASE exampledb OWNER dbuser

The fourth thing is to give all permissions to the exampledb database to dbuser, otherwise dbuser can only log on to the console and does not have any database operation rights.

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser

Finally, exit the console using the\ Q command (you can also press ctrl+D directly).

\ Q

Second, use the shell command line

Adding new users and databases can be done not only in the PostgreSQL console, but also on the shell command line. This is because PostgreSQL provides command-line programs createuser and createdb. Take the new user dbuser and database exampledb as an example.

First, create the database user dbuser and specify it as a superuser.

Sudo-u postgres createuser-- superuser dbuser

Then, log in to the database console, set the password for the dbuser user, and exit the console when you are finished.

Sudo-u postgres psql\ password dbuser\ Q

Next, under the shell command line, create the database exampledb and specify the owner as dbuser.

Sudo-u postgres createdb-O dbuser exampledb III. Login database

After you add a new user and a new database, log in to the database as the new user, using the psql command.

Psql-U dbuser-d exampledb-h 127.0.0.1-p 5432

The parameters of the above command are as follows:-U specifies the user,-d specifies the database,-h specifies the server, and-p specifies the port.

After entering the above command, you will be prompted for the password of the dbuser user. If you enter it correctly, you can log in to the console.

The psql command has an abbreviated form. If the current Linux system user is also a PostgreSQL user, you can omit the user name (part of the-U parameter). For example, if my Linux system user name is ruanyf and there is a user with the same name in the PostgreSQL database, then after I log in to the Linux system as ruanyf, I can log in to the database directly using the following command without a password.

Psql exampledb

At this point, if there is a database with the same name as the current system user inside the PostgreSQL, even the database name can be omitted. For example, suppose there is a database called ruanyf, and you can log in to it by typing psql directly.

Psql

In addition, if you want to recover external data, you can use the following command.

Psql exampledb IV. Console commands

In addition to the previously used\ password command (set password) and\ Q command (exit), the console provides a series of other commands.

\ h: check the explanation of the SQL command, such as\ h select. \?: view a list of psql commands. \ l: list all databases. \ C [database_name]: connect to other databases. \ d: lists all tables for the current database. \ d [table_name]: lists the structure of a table. \ du: list all users. \ e: open a text editor. \ conninfo: lists the current database and connection information. 5. Database operation

The basic database operation is to use the general SQL language.

# create a new table CREATE TABLE user_tbl (name VARCHAR (20), signup_date DATE); # insert data INSERT INTO user_tbl (name, signup_date) VALUES ('Zhang San', '2013-12-22'); # Select record SELECT * FROM user_tbl;# update data UPDATE user_tbl set name ='Li Si 'WHERE name =' Zhang San'; # Delete record DELETE FROM user_tbl WHERE name ='Li Si' # add field ALTER TABLE user_tbl ADD email VARCHAR (40); # update structure ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;# rename field ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;# delete field ALTER TABLE user_tbl DROP COLUMN email;# table rename ALTER TABLE user_tbl RENAME TO backup_tbl;# delete form DROP TABLE IF EXISTS backup_tbl At this point, the study on "how to install and use postgresql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.