在windows下手動初始化PostgreSQL資料庫教程，初始化postgresql

在windows下手動初始化PostgreSQL資料庫教程，初始化postgresql

環境：win7 64 sp1
PG：9.3.5

1、建立使用者postgres,密碼同樣是postgres:

net user postgres postgres /add

2、在資料庫根目錄下建立data目錄：

C:\Program Files\PostgreSQL\9.3>md data

3、去掉administrator對data目錄的許可權：

C:\Program Files\PostgreSQL\9.3>cacls data /e /t /r administrator

處理的目錄: C:\Program Files\PostgreSQL\9.3\data
4、將data目錄的許可權賦給postgres使用者：

C:\Program Files\PostgreSQL\9.3>cacls data /e /t /g postgres:C

處理的目錄: C:\Program Files\PostgreSQL\9.3\data
5、用postgres使用者開啟cmd

C:\Program Files\PostgreSQL\9.3>runas /user:postgres cmd.exe

輸入 postgres 的密碼:
試圖將 cmd.exe 作為使用者 "SURE-PC\postgres" 啟動...
6、進行初始化，初始化使用者為postgres,字元集為utf8，locale為C：

C:\Program Files\PostgreSQL\9.3\bin>initdb.exe -D ../data -E utf8 --locale C -U postgresThe files belonging to this database system will be owned by user "postgres".This user must also own the server process. The database cluster will be initialized with locale "C".The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory ../data ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBcreating configuration files ... okcreating template1 database in ../data/base/1 ... okinitializing pg_authid ... okinitializing dependencies ... okcreating system views ... okloading system objects' descriptions ... okcreating collations ... not supported on this platformcreating conversions ... okcreating dictionaries ... oksetting privileges on built-in objects ... okcreating information schema ... okloading PL/pgSQL server-side language ... okvacuuming database template1 ... okcopying template1 to template0 ... okcopying template1 to postgres ... oksyncing data to disk ... ok WARNING: enabling "trust" authentication for local connectionsYou can change this by editing pg_hba.conf or using the option -A, or--auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using:   "postgres" -D "../data"or  "pg_ctl" -D "../data" -l logfile start  C:\Program Files\PostgreSQL\9.3\bin>

7、啟動資料庫，進入用戶端：

C:\Program Files\PostgreSQL\9.3\bin>"pg_ctl" -D "../data" startserver starting C:\Program Files\PostgreSQL\9.3\bin>LOG: database system was shut down at 2014-09-18 10:19:54 HKTLOG: autovacuum launcher started C:\Program Files\PostgreSQL\9.3\bin>LOG: database system is ready to accept connections C:\Program Files\PostgreSQL\9.3\bin>psql.exepsql (9.3.5)Type "help" for help. postgres=#

PostgreSQL怎匯入資料

windows下
運行pg_dumpall > outfile

linux下
psql -f outfile postgres

如果是在兩台不同的電腦上，虛擬機器當然也算,同時啟動兩台電腦的postgres服務

可以
pg_dump -h host1 dbname | psql -h host2 dbname

如果資料庫比較簡單，建議直接在新的資料庫中，建立表等邏輯結構，在通過舊的資料庫中運行copy table to file with csv
新的資料庫中運行copy table from file with csv匯入資料

其他方式和具體情況可以看文檔www.postgresql.org/...p.html
 
怎用命令開啟postgresql資料庫

開啟指什嗎？啟動服務：pg_ctl -D 資料集名 start （或者 postgres -D 資料集名）
串連資料庫：psql 資料庫名