PosgreSQL Basic

淡淡的烟草味﹌ 2022-08-05 01:16 173阅读 0赞

## 1. Install postgresql postgresql-contrib ##

camoss@cd-devel15:~$ sudo apt-get install postgresql postgresql-contrib

This will install the latest version available in your Ubuntu release and the commonly used add-ons for it.

## 2. Connect to PosgreSQL server ##

camoss@cd-devel15:~$ sudo -u <user> psql <database>  
  
Note: 大多数PostgreSQL都会利用现有的Linux系统账户来认证PostgreSQL，所以，在安装PostgreSQL时，系统上必须有一个postgres的账户。  
postgres账户作为PostgreSQL的管理员账户，而不是root。  
基于PostgreSQL这种账户管理，我们必须以Linux账户postgres(或自己为Linux和PostgreSQL创建好的账户)来访问PostgreSQL服务器。  
因此，你将看到PostgreSQL的管理员账户postgres在文件/etc/passwd中。  
camoss@cd-devel15:~$   
camoss@cd-devel15:~$ grep -i "postgres" /etc/passwd  
postgres:x:113:122:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash  
camoss@cd-devel15:~$   
  
e.g.  
camoss@cd-devel15:~$ sudo -u postgres psql postgres

## 3. Set password for role/user of PostgreSQL ##

postgres=\# \\password <user>  
  
e.g.   
Set a password for the "postgres" database role  
postgres=\# \\password postgres  
  
pwd: postgres\_123

## 4. Show help information  ##

### Show help information about the backslash commands ###

postgres=\# \\?

### Show help information about SQL commands ###

postgres=\# \\h

## 5. Exit PosgreSQL server ##

Control+D  
or  
postgres=\# \\q

## 6. Create database ##

To create the first database, which we will call "demodb", simply type:  
sudo -u postgres createdb demodb  
or  
camoss@cd-devel15:~$ sudo -u postgres psql postgres   
postgres=\# CREATE DATABASE testdb;

## 7. List all Databases ##

List the names, owners, character set encodings, and access privileges of all the databases in the server.   
If + is appended to the command name, database sizes, default tablespaces, and descriptions are also displayed.   
(Size information is only available for databases that the current user can connect to.)  
  
postgres=\# \\l+ (or \\list+)

## 8. Connect to Database ##

postgres=\# \\c or \\connect \[ dbname \[ username \] \[ host \] \[ port \] \]

## 9. Create linux user for PosgreSQL ##

camoss@cd-devel15:~$ adduser demouser  
  
pwd: demopwd

## 10. Create/Drop database role/user ##

camoss@cd-devel15:~$ sudo -u postgres createuser demouser

camoss@cd-devel15:~$ sudo -u postgres dropuser demouser

or   
postgres=\# CREATE ROLE demouser;  
postgres=\# ALTER ROLE demouser login;  
or

postgres=\# CREATE ROLE demouser login;

Note: 我试了一下sudo -u postgres createuser <user/role> 和 CREATE ROLE <role/user> login 创建的user/role效果一样。

可以通过\\du或\\dg来查看database roles/users

e.g.

### Creates the user with no database creation rights (-D) with no add user rights (-A) and will prompt you for entering a password (-P) ###

camoss@cd-devel15:~$ sudo -u postgres createuser -D -A -P myuser

### Create the database 'mydb' with 'myuser' as owner ###

camoss@cd-devel15:~$ sudo -u postgres createdb -O myuser mydb

## 11. Alter database ##

postgres=\# ALTER DATABASE name RENAME TO new\_name  
postgres=\# ALTER DATABASE name OWNER TO new\_owner  
postgres=\# ALTER DATABASE name SET TABLESPACE new\_tablespace  
  
e.g.  
demodb=\#   
demodb=\# ALTER DATABASE demodb OWNER TO demouser;  
ALTER DATABASE  
demodb=\#   
demodb=\# \\l

## 12. Connect to database(demodb) of PosgreSQL serveras above user(demouser) ##

Note:  
database "demodb" --> which is created by above step 7  
user "demouser" --> which is created by above steps 10 and 11  
  
e.g.   
camoss@cd-devel15:~$   
camoss@cd-devel15:~$ sudo -u demouser psql demodb  
psql (9.3.6)  
Type "help" for help.  
demodb=>   
demodb=>

## 13. List Users and Roles ##

\\du\[+\] \[ pattern \]  
Lists database roles. (Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \\dg.)   
If pattern is specified, only those roles whose names match the pattern are listed.   
If the form \\du+ is used, additional information is shown about each role; currently this adds the comment for each role.  
  
\\dg\[+\] \[ pattern \]  
Lists database roles. (Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \\du.)   
If pattern is specified, only those roles whose names match the pattern are listed.

If the form \\dg+ is used, additional information is shown about each role; currently this adds the comment for each role.

## 14. Enable PostgreSQL to listen across different networks ##

Edit file /etc/postgresql/9.3/main/postgresql.conf, and alter the listen\_addresses   
e.g. Listen on all network interfaces as follow:  
listen\_addresses = '\*'

## 15. Reload/Restart PostgreSQL Server ##

### Configuring the networking / users may need to reload the server ###

camoss@cd-devel15:~$ sudo /etc/init.d/postgresql reload

### Some settings changes in postgresql.conf require a full restart ###

camoss@cd-devel15:~$ sudo /etc/init.d/postgresql restart