Common postgresql commands

Common postgresql commands (1) User utility: createdb creates a new PostgreSQL database (same as the SQL statement: CREATEDATABASE) createuser creates a new PostgreSQL user (same as the SQL statement: same as CREATEUSER) dropdb delete database dropuser delete user pg_dump PostgreS

Common postgresql commands (1) User utility: createdb creates a new PostgreSQL DATABASE (same as the SQL statement: create database) createuser creates a new PostgreSQL user (same as the SQL statement: create user is the same) dropdb delete database dropuser delete USER pg_dump will PostgreS

Common postgresql commands

(1) User utility:

Createdb creates a new PostgreSQL DATABASE (same as SQL statement: CREATE DATABASE)

Createuser creates a new PostgreSQL USER (same as SQL statement: CREATE USER)

Dropdb deletes a database

Dropuser: delete a user

Pg_dump exports the PostgreSQL database to a script file

Pg_dumpall: export all PostgreSQL databases to one script file.

Pg_restore restores the PostgreSQL database from a script file exported by pg_dump or pg_dumpall.

Psql: a PostgreSQL Interactive Client Program Based on command line

Vacuumdb clears and analyzes a PostgreSQL database. It is the shell script encapsulation of the SQL statement VACUUM in the psql environment of the client program. The two functions are identical.

(2) system utilities

1. pg_ctl start, stop, and restart the PostgreSQL service (for example, pg_ctl start starts the PostgreSQL service, which is the same as service postgresql start)

2. pg_controldata displays the internal control information of the PostgreSQL service

3. Switch psql to PostgreSQL's pre-defined database superuser postgres, enable the client program psql, and connect to the desired database, for example:

Psql template1

The following interface is displayed, indicating that you have entered the desired database and can perform the desired operation.

Template1 = #

(3). Some commands in the database:

Template1 =#\ l view existing databases in the system

Template1 =#\ q exit the client program psql

Template1 =#\ c transfers data from one database to another, for example, template1 =#\ c sales transfers data from template1 to sales

Template1 =#\ dt

Template1 =#\ d view the table structure

Template1 =#\ di View index

[Basic database operations] ============================

1. * create a database: create database [database name];

2. * view the Database List: \ d

3. * Delete A database:. drop database [database name];

Create table: create table ([field name 1] [Type 1] ;, [Field name 2] [Type 2],... <, primary key (field name m, field name n,...)> ;);

* View the table name list: \ d

* View the status of a table: \ d [Table name]

* Rename A table: alter table [table Name A] rename to [table name B];

* Delete A table: drop table [table name]; ==========================================================

[Basic operations in a table] ============================

* Add fields to an existing table: alter table [table name] add column [field name] [type];

* Delete fields in a table: alter table [table name] drop column [field name];

* Rename A field: alter table [table name] rename column [field name A] to [field name B];

* Set the default value for a field: alter table [table name] alter column [field name] set default [new default value];

* Remove the default value: alter table [table name] alter column [field name] drop default;

Insert data into the table: insert into Table Name ([field name m], [field name n],...) values ([column m value], [column n value],...);

Modify the data of a column in a row in a table: update [Table name] set [target field name] = [target value] where [feature of this row];

Delete a row in a table: delete from [Table name] where [row features];

Delete from [Table name]; -- empty entire table ============================================ ========================

(4). PostgreSQL User Authentication

Pg_cmd.conf In the PostgreSQL data directory is used for user authentication, which can be found in/usr/local/pgsql/data.

You can take a look at the following examples:

(1) allow any identity on the local machine to connect to any database

Type database user IP-ADDRESS IP-MASK METHOD

Local all trust (unconditional connection)

(2) allow any host whose IP address is 192.168.1.x to connect to the sales database

Type database user IP-ADDRESS IP-MASK METHOD

Host sales all 192.168.1.0 255.255.255.0 ident sameuser (indicating that any operating system user can connect to a database user with the same name)

(5) After reading so much, let's take a complete example of creating a PostgreSQL database user.

(1) Access PostgreSQL advanced users

(2) Enable the client program and enter the template1 Database

Psql template1

(3) create a user

Template1 = # create user hellen with encryped password 'zhenzhen'

(4) because the password is set, you need to edit pg_mirror.conf to synchronize the user and the configuration file.

Add md5 to the original record

Local all hellen md5

(4) log on to the database with a new user

Template1 =#\ q

Psql-U hellen-d template1

PS: To switch users in a database, run the following command:

Template1 = #\! Psql-U tk-d template1

(6). Set specific User Permissions

The following example is used to describe:

Create a user group:

Sales = # create group sale;

Add several users to the group

Sales = # alter group sale add user sale1, sale2, sale3;

Grant user-level sale SELECT permissions for table employee and products

Sales = # grant select on employee, products to group sale;

Delete user user2 in sale

Sales = # alter group sale drop user sale2;

(7). Back up the database

You can use pg_dump and pg_dumpall. For example, back up the sales database:

Pg_dump sales>/home/tk/pgsql/backup/1.bak