Ubuntu PostgreSQL installation and configuration
1. Installation#
1、 installation##
Use the following command to automatically install the latest version, here is 9.5
sudo apt-get install postgresql
After the installation is complete, the default will:
(1) Create a Linux user named "postgres"
(2) Create a default database account named "postgres" without a password as the database administrator
(3) Create a table named "postgres"
Some default information after installation is as follows:
config /etc/postgresql/9.5/main
data /var/lib/postgresql/9.5/main
locale en_US.UTF-8
socket /var/run/postgresql
port 5432
2、 psql command##
After installation, there will be a PostgreSQL client psql. Enter through sudo -u postgres psql, the prompt becomes: postgres=#
The basic commands for executing SQL statements and psql are available here. The basic commands available are as follows:
\ password: set password
\ q: exit
\ h: View the explanation of SQL commands, such as\h select。
\? : View the list of psql commands.
\ l: List all databases.
\ c [database_name]: Connect to other databases.
\ d: List all tables of the current database.
\ d [table_name]: List the structure of a table.
\ du: List all users.
\ e: Open a text editor.
\ conninfo: List the current database and connection information.
2. Modify the password of the database default account#
1、 log in##
The command to log in to the database using the psql command is:
psql -U dbuser -d exampledb -h 127.0.0.1-p 5432
The meaning of the parameters of the above command is as follows: -U specifies the user, -d specifies the database, -h specifies the server, and -p specifies the port.
After entering the above command, the system will prompt for the password of the dbuser user.
The psql command has a shorthand form:
If the current Linux system user is also a PostgreSQL user, you can omit the user name (the part of the -U parameter)
If there is a database with the same name as the current system user in PostgreSQL, the database name can also be omitted.
2、 Modify the password of the default administrator account##
Execute the psql client as the Linux user "postgres" (only this user has the psql command at this time) and enter the prompt interface of the client (here the system user name, database user name, and database name are all postgres, so abbreviations can be used form)
sudo -u postgres psql
postgres=# alter user postgres with password '123456';
In this way, the password of the administrator "postgres" is "123456".
Exit the psql client command: \q
To delete the administrator’s password, you can use the command: sudo -u postgres psql -d postgres
Three, modify the password of the Linux user#
This actually has little to do with installing postgresql.
Take the Linux user "postgres" as an example and run the passwd command on it:
zsm@ubuntu:/etc/postgresql/9.5/main$ sudo -u postgres passwd //You can also sudo passwd postgres
Changing password for postgres.(current) UNIX password:
Enter newUNIX password:
Retype newUNIX password:
passwd: password updated successfully
Fourth, configure the database to allow remote connection access#
After the installation is complete, the database can only be connected locally by default, and other machines cannot access it. Configuration is required. (The following example opens the maximum connection permission, the actual configuration depends on your needs)
1、 Modify listening address##
sudo gedit /etc/postgresql/9.5/main/postgresql.conf
Remove the comment #listen_addresses ='localhost' and change it to listen_addresses ='*'
2、 Modify the IP segment of accessible users##
sudo gedit /etc/postgresql/9.5/main/pg_hba.conf
Add at the end of the file: host all all 0.0.0.0 0.0.0.0 md5, which means that any IP connection is allowed
3、 Restart the database##
sudo /etc/init.d/postgresql restart
Others: manage users, establish databases, etc.
Five, add new users and new databases#
Method 1: Use PostgreSQL client psql
Run the psql command of the system user "postgres" to enter the client:
sudo -u postgres psql
Create user "xiaozhang" and set password:
postgres=# create user xiaozhang with password '123456';
Create database exampledb, the owner is xiaozhang:
postgres=# create database exampledb owner xiaozhang;
Assign all permissions of the exampledb database to xiaozhang, otherwise xiaozhang can only log in to psql without any database operation permissions:
grant all privileges on database exampledb to xiaozhang;
Method 2: Use the shell command line##
After installing PostgreSQL, the createuser and createdb command line programs are provided.
First create the database user "xiaozhang1" and assign it as a super user:
sudo -u postgres createuser --superuser xiaozhang1;
Then log in to the psql console to set its password and exit:
zsm@ubuntu:~$ sudo -u postgres psql
psql(9.5.3)
Type "help"for help.
postgres=# \password xiaozhang1;
Enter newpassword:
Enter it again:
postgres=# \q
Then create the database and specify the owner under the shell command line:
sudo -u postgres createdb -O xiaozhang1 exampledb1;
Method 3: Use paadmin3 to connect to the database as an administrator and create
After the first and second operations, execute postgres=# \du to get the user list as follows:
Execute postgres=# \l to get the database list as follows:
To delete a user (such as deleting xiaozhang), first postgres=# drop database example; and then postgres=# drop user xiaozhang;.
Six, basic database operation commands#
# Create 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;
# Form rename
ALTER TABLE user_tbl RENAME TO backup_tbl;
# Delete table
DROP TABLE IF EXISTS backup_tbl;
Seven, reference materials#
Recommended Posts