Summary of Postgres Common commands

One: Command line Backup and recovery Postgres database

①: First switch to Postgres user
Su Postgres

②: Backing up a database named host to a temporary file Host.dump
Pg_dump-w Host >/tmp/host.dump

③: Import data from backup file infile to database named dbname
Psql dbname < infile

Second: Update a data record in a datasheet

①: Log in to the database as a postgres user
sudo su postgres-c psql template1

②: Connecting to a database database
\c DatabaseName

③: Querying the Data in config table
Select Configid,key from config;

④: Value with the new Value field
Update config set value= ' where configid= ' 50ce7b11-53c3-4e5f-e02-

E4df37af51aa18 ';

Three: Create a postgres database

①: Log in as Postgres user

Su Postgres Enter postgres user's password

②: Creating a Database

Createdb Host

Four: Make PostgreSQL script, create postgres Chinese database

echo "Install database ..."
#install Postgres from source
process=$ (cat/etc/passwd |grep postgres)
If [-Z "$process"]; Then

Useradd Postgres
Else
echo "Postgres:postgres1" >/tmp.txt
CHPASSWD </tmp.txt
Rm-f/tmp.txt
Fi
process=$ (cat/etc/postgresql/8.4/main/pg_hba.conf |grep 0/0)
proces1=$ (cat/etc/ssh/sshd_config |grep "allowusers admin")
If [-Z "$process"] && [z "$proces 1"]; Then
echo "host all All 0.0.0.0/0 trust" >>/etc/postgresql/8.4/main/pg_hba.conf
Sed-i ' s/#listen_addresses/listen_addresses/g '

/etc/postgresql/8.4/main/postgresql.conf
Sed-i ' s/localhost/*/g '/etc/postgresql/8.4/main/postgresql.conf
Fi

#change password for user:p ostgres in database
/etc/init.d/postgresql-8.4 restart
Sleep 5
Su-postgres << EOF
Psql-c "Alter user postgres with password ' Postgres1 '" <<e2
E2
Psql-c "CREATE database Cloud with encoding= ' sql_ascii ' lc_collate= ' C '

Lc_ctype= ' C ' template template0 CONNECTION LIMIT =-1; "<<e3
E3
Eof

Learning URL: Http://wiki.ubuntu.org.cn/PostgreSQL