最近需要使用Django开发个Web App. Django官方推荐使用Postgresql作为数据库,所以这里记录一下在Mac OSX上安装的步骤和碰到的问题。
最简单的方式是安装Postgres.App. 这个应用里自带了最新版本的PostgreSQL而且不需要学习数据库服务器启动和关闭的命令。程序安好后(别忘了拖拽到Application的文件夹里),会自动在端口5432开启一个PostgreSQL的服务器。在程序界面里还有一个很贴心的按钮 'Open psql',可以自动为你在命令行里打开一个客户端与服务器进行连接。而且它会使用你当前的Mac用户名在服务器上为你注册成为一个superuser,让你立刻就可以在数据库进行任何的修改和操作。
通过Python与PostgreSQL建立连接和操作需要安装psycopg2这个库。可以通过一下指令来安装:
$ pip install psycopg2
这个时候会出现一个错误:
Error: pg_config executable not found.
Please add the directory containing pg_config to the PATH
再次求助StackOverflow找到了答案,psycopg2在安装的时候需要pg_config这个程序。这个程序其实已经随着Postgres.app安装到了硬盘上,但是还没有被添加到系统的PATH里。以下是添加方式:
$ cd ~
$ nano .bash_profile
然后在bash_profile里添加, 你可能需要修改版本号码(9.4):
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.4/bin
保存文件后重新加载bash_profile,注意两个点之间的空格:
$ . .bash_profile
确认pg_config确实可以被系统找到:
$ which pg_config
/Applications/Postgres.app/Contents/Versions/9.4/bin/pg_config
然后再次安装psycopg2就好了
$ pip install psycopg2
以上。
Sometimes, we need to dump a database from production server and import it into development machine for testing purpose, to do that:
- use the following command to dump the database first:
su - postgres
pg_dump database_name > file_name
For instance, if I have a database called note, I would dump it as (this is done in shell not in psql):
pg_dump note > note.db.bak
Before importing note.db.bak into your local postgres, a few things need to be taken care of first:
1) You have to have the same list of users on your local machine as on your server. Use \du inside psql to list users on your server. Then, recreate them on your local machine in psql
create user user_name;
Users created on your local machine do not have to mirror the same privileges they have on the server.
2) Create the database that you want to import the backup into. In psql,
create database note_server_bak;
Now, you can import the data with the following command in shell:
psql -1 note_server_bak < note.db.bak
Note that the option after psql is 'dash one' not 'dash L'. This option means while importing, everything will be wrapped in a transaction so that if the import fails, nothing will be stored in note_server_bak, in other words, note_server_bak stays clean.
If you don't use -1 and an error occurs during importing, all of the data before the error will be imported into the database, which leaves your database with incomplete data. When you import the next time, you are going to see even more errors because of this.