Cheng's Blog

  • About Me
  • Archive
  • feeds

Posts match “ postgres ” tag:

over 7 years ago

在Mac OSX 上安装PostgreSQL和psycopg2

最近需要使用Django开发个Web App. Django官方推荐使用Postgresql作为数据库,所以这里记录一下在Mac OSX上安装的步骤和碰到的问题。

  1. 最简单的方式是安装Postgres.App. 这个应用里自带了最新版本的PostgreSQL而且不需要学习数据库服务器启动和关闭的命令。程序安好后(别忘了拖拽到Application的文件夹里),会自动在端口5432开启一个PostgreSQL的服务器。在程序界面里还有一个很贴心的按钮 'Open psql',可以自动为你在命令行里打开一个客户端与服务器进行连接。而且它会使用你当前的Mac用户名在服务器上为你注册成为一个superuser,让你立刻就可以在数据库进行任何的修改和操作。

  2. 通过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

以上。

  • postgres
  • January 17, 2015 11:46
  • Permalink
  • Comments
 
almost 7 years ago

Postgres dump database

Sometimes, we need to dump a database from production server and import it into development machine for testing purpose, to do that:

  1. 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.

  • postgres
  • June 09, 2015 12:55
  • Permalink
  • Comments
 

Copyright © 2013 GuoCheng . Powered by Logdown.
Based on work at subtlepatterns.com.