Postgres常用命令总结

①：首先切换为postgres用户

su postgres

②：备份名为host的数据库到临时文件host.dump

pg_dump -W host > /tmp/host.dump

③：从备份文件infile中导入数据到数据库名为dbname的数据库

psql dbname < infile

二：更新数据表中的某条数据记录

①：以postgres用户登录到数据库中

sudo su postgres -c psql template1

②：连接一个数据库数据库

\c databasename

③：查询config表中的数据

select configid,key from config;

④：跟新value字段的值

update config set value='29' where configid='50ce7b11-53c3-4e5f-e02- e4df37af51aa18';

三：创建postgres数据库

①：以postgres用户登录

su postgres 输入postgres用户的密码

②：创建数据库

createdb host

四：制作PostgreSQL脚本，创建postgres中文数据库

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 "$proces1" ]; 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 :postgres in database

/etc/init.d/postgresql-8.4 restart

sleep 5

su - postgres << EOF

psql -c "alter user postgres with password 'Postgres1'" <

E2

psql -c "create database cloud with encoding='SQL_ASCII'

LC_COLLATE='C'

LC_CTYPE='C' template template0 CONNECTION LIMIT = -1;" <

EOF