pgsql操作數(shù)據(jù)庫命令

2019-11-08 20:55:54

字體：大中小

來源：轉載

供稿：網(wǎng)友

關于postgresql——常用操作指令創(chuàng)建數(shù)據(jù)庫CREATE DATABASE test WITH OWNER = postgres ENCODING = 'UTF8';進入控制臺方法，在postgreSQL的安裝目的bin下執(zhí)行命令： psql 數(shù)據(jù)庫名，例： /usr/local/pgsql/bin/psql mydb指定host、用戶名和數(shù)據(jù)庫的話，如：/usr/local/pgsql/bin/psql -h localhost -U postgres -d test查看版本： psql --version 或 SELECT version();查看所有數(shù)據(jù)庫：/l查看所有數(shù)據(jù)庫（包括詳細參數(shù)）：select * from pg_database;選擇數(shù)據(jù)庫：/c databasename查看所有表：/dt schema_name.* 查看某個表的結構：/d schema_name.tablename退出psql控制臺：/q查看表的索引：select * from pg_indexes where tablename='log';導出備份數(shù)據(jù)庫：pg_dump -h localhost -U postgres databasename > /tmp/databasename.bak.yyyymmdd.sql(或者用如下命令：#su postgres

#psql

說明：

1.(psql -U immuser -h 127.0.0.1 -d newPRo 表示使用newpro數(shù)據(jù)庫的immuser用戶登錄本地服務器的newpro數(shù)據(jù)庫

2.（psql -U postgres -h 127.0.0.1 表示使用postgres 用戶登錄本地數(shù)據(jù)庫）)#/l#/q#cd (這里cd后面直接按回車鍵，表示進入到postgres用戶的home目錄，linux是多用戶的，cd后面不加任何參數(shù)表示，進入當前用戶的家目錄中)#pg_dump databasename > databasename.sql)

下面是數(shù)據(jù)庫備份與恢復詳細過程：

導入恢復數(shù)據(jù)庫(sql文件是pg_dump導出的文件就行，可以是整個數(shù)據(jù)庫，也可以只是單個表，也可以只是結構等)： mkdir -p /var/lib/pgsql/9.4/data/newpro_space #創(chuàng)建存放表空間的目錄chown -R postgres:postgres /var/lib/pgsql/9.4/data/newpro_space#修改其所屬組(1).創(chuàng)建表空間CREATE TABLESPACE newpro_sp OWNER immuser LOCATION '/var/lib/pgsql/9.4/data/newpro_space';-- Database: usercenter-- DROP DATABASE usercenter;(2).創(chuàng)建數(shù)據(jù)庫，因為備份數(shù)據(jù)庫時只能備份表的結構和內容，所以在回復數(shù)據(jù)庫的時候要先創(chuàng)建數(shù)據(jù)庫，用于恢復數(shù)據(jù)；CREATE DATABASE newpro WITH OWNER = immuser ENCODING = 'UTF8' TABLESPACE = newpro_sp LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;psql -h localhost -U postgres -d databasename < /tmp/databasename.bak.yyyymmdd.sql(或者用如下命令：#su postgres#cd (這里cd后面直接按回車鍵，表示進入到postgres用戶的home目錄，linux是多用戶的，cd后面不加任何參數(shù)表示，進入當前用戶的家目錄中)psql databasename < databasename.sql)導出數(shù)據(jù)結構，主要是加上參數(shù)-s：pg_dump -U username -W dbname -f /tmp/filename.sql導出某個表：pg_dump -h localhost -U postgres -t tablename dbname > test.sql導出某個表的結構，同樣是加參數(shù)"-s"：pg_dump -h localhost -U postgres -t tablename -s dbname > test_construct.sql導出某個表的數(shù)據(jù)，加參數(shù)"-a"：pg_dump -h localhost -U postgres -t tablename -a dbname > test_data.sql查看序列：select * from information_schema.sequences where sequence_schema = 'public';查看數(shù)據(jù)庫大小：select pg_size_pretty(pg_database_size('test'));

查看表的大小：select pg_size_pretty(pg_relation_size('test'));

####為了便于數(shù)據(jù)庫的統(tǒng)一管理與減少pgsql的安裝將三臺服務器數(shù)據(jù)庫全部搭建在87服務器上#######10.0.1.87###首先要先將101和70服務器上的數(shù)據(jù)庫備份.sql文件上傳到87服務器上；然后按照如下步驟操作：a).恢復newpro數(shù)據(jù)庫： mkdir -p /var/lib/pgsql/9.4/data/newpro_space #創(chuàng)建存放表空間的目錄chown -R postgres:postgres /var/lib/pgsql/9.4/data/newpro_space#修改其所屬組#su postgres#psql(1).創(chuàng)建表空間CREATE TABLESPACE newpro_sp OWNER immuser LOCATION '/var/lib/pgsql/9.4/data/newpro_space';-- Database: newpro;-- DROP DATABASE newpro;(2).創(chuàng)建數(shù)據(jù)庫，因為備份數(shù)據(jù)庫時只能備份表的結構和內容，所以在回復數(shù)據(jù)庫的時候要先創(chuàng)建數(shù)據(jù)庫，用于恢復數(shù)據(jù)；CREATE DATABASE newpro WITH OWNER = immuser ENCODING = 'UTF8' TABLESPACE = newpro_sp LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;#/qpsql -h localhost -U postgres -d newpro< newpro.sql(或者用如下命令：#su postgres#cd (這里cd后面直接按回車鍵，表示進入到postgres用戶的home目錄，linux是多用戶的，cd后面不加任何參數(shù)表示，進入當前用戶的家目錄中)psql newpro< newpro.sql)b).恢復usercenter數(shù)據(jù)庫： mkdir -p /var/lib/pgsql/9.4/data/usercenter_space #創(chuàng)建存放表空間的目錄chown -R postgres:postgres /var/lib/pgsql/9.4/data/usercenter_space#修改其所屬組#su postgres#psql(1).創(chuàng)建表空間CREATE TABLESPACE usercenter_sp OWNER immuser LOCATION '/var/lib/pgsql/9.4/data/usercenter_space';-- Database: usercenter;-- DROP DATABASE usercenter;(2).創(chuàng)建數(shù)據(jù)庫，因為備份數(shù)據(jù)庫時只能備份表的結構和內容，所以在回復數(shù)據(jù)庫的時候要先創(chuàng)建數(shù)據(jù)庫，用于恢復數(shù)據(jù)；CREATE DATABASE usercenter WITH OWNER = immuser ENCODING = 'UTF8' TABLESPACE = usercenter_sp LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;#/qpsql -h localhost -U postgres -d usercenter< usercenter.sql(或者用如下命令：#su postgres#cd (這里cd后面直接按回車鍵，表示進入到postgres用戶的home目錄，linux是多用戶的，cd后面不加任何參數(shù)表示，進入當前用戶的家目錄中)psql usercenter< usercenter.sql)c).恢復teamtalk數(shù)據(jù)庫mkdir -p /var/lib/pgsql/9.4/data/teamtalk_space #創(chuàng)建存放表空間的目錄chown -R postgres:postgres /var/lib/pgsql/9.4/data/teamtalk_space#修改其所屬組#su postgres#psqlSET statement_timeout = 0;SET client_encoding = 'UTF8';SET standard_conforming_strings = on;SET check_function_bodies = false;SET client_min_messages = warning;CREATE SCHEMA teamtalk;ALTER SCHEMA teamtalk OWNER TO immuser;(1).創(chuàng)建表空間CREATE TABLESPACE teamtalk_sp OWNER immuser LOCATION '/var/lib/pgsql/9.4/data/teamtalk_space';-- Database: teamtalk;-- DROP DATABASE teamtalk;(2).創(chuàng)建數(shù)據(jù)庫，因為備份數(shù)據(jù)庫時只能備份表的結構和內容，所以在回復數(shù)據(jù)庫的時候要先創(chuàng)建數(shù)據(jù)庫，用于恢復數(shù)據(jù)；CREATE DATABASE teamtalk WITH OWNER = immuser ENCODING = 'UTF8' TABLESPACE = teamtalk_sp LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;#/q

psql -h localhost -U postgres -d teamtalk< teamtalk.sql(或者用如下命令：#su postgres#cd (這里cd后面直接按回車鍵，表示進入到postgres用戶的home目錄，linux是多用戶的，cd后面不加任何參數(shù)表示，進入當前用戶的家目錄中)psql teamtalk< teamtalk_new.sql)

上一篇：給SQL查詢結果加上序號

下一篇：Tips: 快速查詢定位最慢的SQL