Pgsql Simple operation

**********************************************

* Basic Operation

**********************************************

Database operations

$ psql Test

$ psql-h localhost-u username-w newpwd-p 5432 Test

=# CREATE DATABASE mail_db;

=# CREATE database mail_db owner Sunny;

=# drop Database mail_db;

Table Operations

=# CREATE TABLE mail_sunny.com (

=# ID int,

=# name varchar (50),

=# City varchar (50),

=# Date Date

=# );

Simple query

$ psql-l

View current library, library master, encoding; pgsql allow automatic transcoding on server and client

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/26/ED/wKiom1Nu_-bDIdpsAABscRs8Up4767.jpg "title=" 1.png " alt= "Wkiom1nu_-bdidpsaabscrs8up4767.jpg"/>

Backup/Restore

$ pg_dump > test_201405111403 (back up a library)

$ psql Test < test_201405111403

$ pg_dump-h localhost Test | Pssql-h 192.168.0.180 Test

$ pg_dumpall > all_201405111407 (back up each database in a given cluster)

Database Super User rights (used to recover user and group information) are required for recovery

$ pg_dump Test | gzip > test.gz

$ createdb Test (Restore the above backup)

$ gzunzip-c test.gz | Psql test

Backing up Shards

$ ps_dump Test | Split-b 10m-test.bak

$ createdb Test

$ Cat Test.bak* | Psql test

**********************************************

* Account Management (user, Group)

**********************************************

Reset Password

=# alter user sunny password ' anotherpwd ';

New user

=# Create user sunny;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/26/ED/wKiom1NvAVyyhluHAAAaBs5pWQk003.jpg "title=" 1.png " alt= "Wkiom1nvavyyhluhaaaabs5pwqk003.jpg"/>

(the user does not have a password; cannot log in to the database)

$ Createruser Sunny

$ Dropuser Sunny

=# Create user sunny PASSWORD ' newpwd ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/26/ED/wKiom1NvAaGDNAOyAAAvFunwLpI001.jpg "title=" 1.png " alt= "Wkiom1nvaagdnaoyaaavfunwlpi001.jpg"/>

There is a echo when the user is successfully added

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/26/ED/wKioL1NvBFjA1j6RAABqIiEOd3o626.jpg "title=" 1.png " alt= "Wkiol1nvbfja1j6raabqiieod3o626.jpg"/>

When you delete a user, there are quite a few traps.

Empowering Users

$ psql Test-u Sunny

=# Create user Hhpeng with password ' Hhpeng ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/26/ED/wKioL1NvAtyhlA50AAA13jZCKvE859.jpg "title=" 1.png " alt= "Wkiol1nvatyhla50aaa13jzckve859.jpg"/>

Default, only advanced users have permission to create;

$ psql test-u Postgres

=# Create user sunny CreateUser;

Authorize when creating a user

=# alter user sunny Createuer;

=# alter user sunny createdb;

=# \q

$ psql Test-u Sunny

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/26/ED/wKioL1NvBm_TCTJEAABRRQRdg2w630.jpg "title=" 1.png " alt= "Wkiol1nvbm_tctjeaabrrqrdg2w630.jpg"/>

=# Create group Tech;

=# Alter group Tech add user sunny;

=# Alter group tech drop user sunny;

------------------------------------

Permissions:

------------------------------------

When a library is created, it belongs to a user (creator). Requires authorization for other users to have access to the library.

Permissions classification: Select Insert Update delete rule references trigger

Create temporary execute usage "all privileges"

=# Grant update on test to sunny;

=# Grant Select on test to group tech;

=# revoke all on the test from Sunny;

=# revoke all on test from public;

Public: Represents all users in the system.

This article from "Little Cui's experimental notes" blog, declined to reprint!