Install and configure PostgreSQL in Ubuntu

Install and configure PostgreSQL in Ubuntu
I. installation 1. Installation

Use the following command to automatically install the latest version. Here, PostgreSQL is 9.5.

sudo apt-get install postgresql

After the installation is complete, the default setting is:

(1) create a Linux user named "s"

(2) create a default database account named "s" without a password as the database administrator.

(3) create a table named "s"

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, the PostgreSQL client psql will be displayed through sudo-u postgres psql, and the prompt will be: postgres = #

Here, you can run SQL statements and basic psql commands. The basic commands available are as follows:

\ Password: Set the password \ q: Exit \ h: view the explanation of the SQL command, for example, \ h select. \? : View the psql command list. \ L: list all databases. \ C [database_name]: connects 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 the text editor. \ Conninfo: lists information about the current database and connection.

2. Change the password of the default database account 1. log on

The command for logging on to the database using the psql command is:

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

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

After entering the preceding command, the system will prompt you to enter the password of dbuser.

Psql commands are abbreviated as follows:

If you are a Linux user and a PostgreSQL user, you can omit the user name (part of the-U parameter)

If PostgreSQL still has a database with the same name as the current system user, the database name can also be omitted.

2. Change the password of the default Administrator Account

Run the psql client as the Linux User "postgres" (only the user has the psql command at this time) to enter the prompt interface of the client (here the system username, database username, and database name are postgres, therefore, the abbreviated form can be used)

sudo -u postgres psql

postgres=# alter user postgres with password '123456'

In this way, the Administrator "s" password is "123456 ".

Exit the psql client command: \ q

To delete the administrator password, run the following command: sudo-u postgres psql-d postgres.

3. change the password of a Linux User

This actually has little to do with installing postgresql.

Take Linux User "s" as an example to run the passwd command on it:

Zsm @ Ubuntu:/etc/postgresql/9.5/main $ sudo-u postgres passwd // sudo passwd postgres

Changing password for postgres.(current) UNIX password: Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully

4. Configure the database to allow remote access

After the installation is complete, you can only connect to the database locally by default. Other hosts cannot access the database and need to be configured.

1. Modify the 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 address segment of an accessible user

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

Add host all 0.0.0.0 0.0.0.0 md5 at the end of the file to run any IP connection.

3. Restart the database

sudo /etc/init.d/postgresql restart

Others: user management and database creation

5. Add new users and new data warehouse Method 1: Use PostgreSQL client psql

Run the psql command of the System user "s" to enter the client:

sudo -u postgres psql

Create the user "xiaozhang" and set the password:

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

Create a database exampledb with the owner xiaozhang:

postgres=# create database exampledb owner xiaozhang;

Grant all permissions of the exampledb database to xiaozhang. Otherwise, xiaozhang can only log on to psql without any database operation permissions:

grant all privileges on database exampledb to xiaozhang;

Method 2: Use shell command line

After PostgreSQL is installed, the createuser and createdb command line programs are provided.

First, create a database user "xiaozhang1" and specify it as a Super User:

sudo -u postgres createuser --superuser xiaozhang1;

Log on to the psql console, set the password, and exit:

zsm@ubuntu:~$ sudo -u postgres psqlpsql (9.5.3)Type "help" for help.postgres=# \password xiaozhang1;Enter new password: Enter it again: postgres=# \q

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

sudo -u postgres createdb -O xiaozhang1 exampledb1;

Method 3: Use paadmin3 to connect to the database as an administrator and create

 

After method 1 and method 2, execute ipvs = # \ du to obtain the following list of users:

Run S =#\ l to obtain the Database List as follows:

To delete a user (such as xiaozhang), postgres = # drop database example; then S = # drop user xiaozhang ;.

Vi. Basic database operation commands

# Create table user_tbl (name VARCHAR (20), signup_date DATE); # INSERT data INTO user_tbl (name, signup_date) VALUES ('zhang san ', '1970-12-22 '); # SELECT * FROM user_tbl; # UPDATE data UPDATE user_tbl set name = 'lily' WHERE name = 'zhangsan '; # DELETE record delete from user_tbl WHERE name = 'lily'; # ADD a column alter table user_tbl ADD email VARCHAR (40); # update the structure alter table user_tbl alter column signup_date set not null; # RENAME the column alter table user_tbl rename column signup_date TO signup; # Delete the column alter table user_tbl drop column email; # RENAME the table alter table user_tbl rename to backup_tbl; # delete a table drop table if exists backup_tbl;

------------------------------------ Lili split line ------------------------------------

Compile and install the PostgreSQL 6.5 database on CentOS 9.3

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: