Introduction to installation and configuration of Ubuntu PostgreSQL

Time:2019-6-30

1. Installation

Using the following command, the latest version will be installed automatically, which is 9.5 here.


sudo apt-get install postgresql

When the installation is completed, the default will be:

  • (1) Create a Linux user named “postgres”
  • (2) Create a default database account named “postgres” with no 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 PostgreSQL client psql, throughsudo -u postgres psql Enter and the prompt becomes:postgres=# 

Here you can use basic commands to execute SQL statements and psql. The basic commands available are as follows:

  • \ password: Set the password
  • \ q: Exit
  • \ h: Check the explanation of the SQL command, such as h select.
  • \?: Check the PSQL command list.
  • \ l: List all databases.
  • \ C [database_name]: Connect to other databases.
  • \ d: List all tables in the current database.
  • \ D [table_name]: Lists the structure of a table.
  • \ du: List all users.
  • \ e: Open the text editor.
  • \ Conninfo: Lists the current database and connection information.

Modify the password of the default account in the database

1. Login

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 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, the system will prompt you to enter the password of the dbuser user user.

The PSQL command exists in abbreviated form:

If the current Linux system user is also a PostgreSQL user, you can omit the username (- U parameter part)

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 password of default administrator account

Execute the PSQL client as a Linux user’s “postgres” (at this time only the user has the PSQL command) and enter the prompt interface of the client (where the system username, database username and database name are postgres, so it can be abbreviated)


sudo -u postgres psql


postgres=# alter user postgres with password '123456';

In this way, the administrator’s “postgres” password is “123456”.

Exit PSQL client command: q

To delete the administrator’s password, you can use the command:sudo -u postgres psql -d postgres

Modifying passwords for Linux users

Take Linux user “postgres” as an example, run passwd command on it:

[email protected]ubuntu:/etc/postgresql/9.5/main$sudo-u Postgres passwd//or sudo passwd Postgres
Changing password for postgres.
(current) UNIX password: 
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

Configure the database to allow remote connection access

After the installation is completed, the default connection to the database can only be local, other computers can not access, need to be configured.

1. Modify the listening address


sudo gedit /etc/postgresql/9.5/main/postgresql.conf

take #listen_addresses = 'localhost' Remove the comment and change it tolisten_addresses = '*'

2. Modifying IP segments of accessible users


sudo gedit /etc/postgresql/9.5/main/pg_hba.conf

Add:host all all 0.0.0.0 0.0.0.0 md5 Represents running any IP connection

3. Restart the database


sudo /etc/init.d/postgresql restart

Add new users and new databases

1. Use PostgreSQL client PSQL

Run the PSQL command of the system user “postgres” and enter the client:


sudo -u postgres psql

Create user “xiaozhang” and set password:


postgres=# create user xiaozhang with password '123456';

Create the database exampledb, owned by xiaozhang:


postgres=# create database exampledb owner xiaozhang;

Give Xiaozhang all the permissions of exampledb database, otherwise Xiaozhang can only login to PSQL without any database operation permissions:


grant all privileges on database exampledb to xiaozhang;

2. Use the shell command line

After installing PostgreSQL, the CREATEUSER and CREATEDB command line programs are provided.

First create the database user “codetc” and specify it as a superuser:


sudo -u postgres createuser --superuser codetc;

Then login to the PSQL console to set its password and exit:


[email protected]:~$ sudo -u postgres psql
psql (9.5.3)
Type "help" for help.
postgres=# \password codetc;
Enter new password: 
Enter it again: 
postgres=# \q

Then create the database under the shell command line and specify the owner:


sudo -u postgres createdb -O codetc exampledb1;

Basic database operation commands

# 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 Records 
SELECT * FROM user_tbl;
# Update data 
UPDATE user_tbl set name ='Lisi'WHERE name ='Zhangsan';
# Delete records 
DELETE FROM user_tbl WHERE name ='Lisi';
# Add fields 
ALTER TABLE user_tbl ADD email VARCHAR(40);
# Update structure 
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# renamed field 
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# Delete fields 
ALTER TABLE user_tbl DROP COLUMN email;
# Table renaming 
ALTER TABLE user_tbl RENAME TO backup_tbl;
# Delete tables 
DROP TABLE IF EXISTS backup_tbl;

Restart service

/etc/init.d/postgresql restart
perhaps
service postgresql restart

uninstall


sudo apt-get purge 'postgresql-*'
sudo apt-get autoremove 'postgresql-*'

summary

Above is the whole content of this article. I hope the content of this article has some reference value for your study or work. Thank you for your support to developpaer. If you want to know more about it, please check the links below.