Tuesday, May 10, 2011

How To Export PostgreSQL Table Data To CSV File

When exporting data from a previous database, most people want to export to CSV format. Here I show you how to do just that. This isn't a fancy or complicated guide to exporting with PostgreSQL, just a quickie for how to export CSV data from a table in PostgreSQL.

Step 1 - Login To PostgreSQL Command Line
Open you terminal (be it Gnome Terminal or Konsole....or xterm for you weirdos). Log into the PostgreSQL command line using the following command:

psql -u <username> -h <hostname> -d <database> -p
Password: <password>


Replace <username>, <hostname>, and <database> with the correct information for your setup. If it requests a password, enter the password for that user. If you just want to access your local user database, simple use "psql" without any parameters and it should work fine.

This command will bring you to a prompt with the current database + "=#". This is how you know you're logged in and ready to go to step 2.

Step 2 - Export To CSV File
The following command can be used to export to a CSV file. Try it out:

copy (select * from mytable) to '/path/to/file.csv' with csv header;

The select query can be replaced with ANYTHING you want. The above example just gets everything from the table. You can replace the query with one that will grab for example the last 100 rows or only rows with the name "Foobar" or something. Your choice!

Replace the '/path/to/file.csv' with the full path to the file you want to export to. Also, above you can remove "with csv header" if you don't want headers in your generated CSV file. I could explain the difference but if you really want to know just run the command with and without the "with csv header" part and you'll see.

Step 3 - In Case You're A Blazing Idiot
Here are a few things to look at if the above doesn't work. Let me know if something doesn't work that's not in this list.
  • Make sure the folder you are pointing to exists. Otherwise the copy command will give you an error saying that the file doesn't exist.
  • You don't need to use the *.csv extension for the CSV file, but you probably should since conventions are good for you! Oh and also so you can know what's in the file later, which helps in the long run.
  • Make sure the file path is inside single quotes only! I tried using no quotes and double quotes, and they don't work.

Step 4 - Knock Yourself Out
Try it out with your own tables! I think even if you don't need to export data into CSV format, it's useful to at least keep that around for when you do. How about bookmarking this page?

Sunday, May 1, 2011

Setup a Rails 3 project with PostgreSQL on Ubuntu 11.04

So I've been working on a cool web application using Ruby on Rails for all the latest innovations in security, solid modelling and also for the extremely useful MVC concepts in use with Rails.

Yesterday I wanted to set up my Rails project to use a PostgreSQL database, so I searched on Google to find out how. After looking to about 10 different pages, I finally figured out how to do it, so here goes. This guide is written for Ubuntu 11.04, but later I'll probably post for openSUSE 11.4 since I've got to do this on there as well.

Step 1 - Install PostgreSQL
If you've already installed it on your machine, you may skip this step.

sudo apt-get install postgresql postgresql-contrib libpq-dev


We need libpq-dev to be able to install the Ruby pg gem. After the setup completes, then run the following commands:

sudo -u postgres createuser --superuser $USER
sudo -u postgres psql postgres


In the first command, using $USER will automatically add your user to PostgreSQL. If you want another user, just replace $USER with the correct user name. The second command will bring you directly into the psql console, and will open up the postgres database. It's just the database corresponding to the postgres user, which for some reason is necessary when installing PostgreSQL.

Once in the psql prompt, type the following command:

postgres=# \passsword <user>


Replace <user> with your actual user name. So anyway, type whatever password you want for your user IN the database twice, then once it returns you to the prompt, type "\q" (without the quotes!) to exit the psql prompt.

Now you'll be back in your terminal, and you can then proceed to create a database for your
user. If you really don't care to have a database for your user, just skip this step.

createdb $USER


Remember to replace $USER with your ACTUAL user name.

Also, you need to change the postgresql.conf file to make PostgreSQL listen on at least localhost. If you have a setup where you want to listen on an external IP or something, change this line to either the IP or just '*' or something.

/etc/postgresql/8.4/main/postgresql.conf:
listen_addresses = 'localhost'


Then, in pg_hba.conf, make sure you've got this:

/etc/postgresql/8.4/main/pg_hba.conf:
# "local" is for Unix domain socket connections only
local   all         all                               md5


Otherwise you'll probably get an error when trying to login from your Rails app. After this, all you've got to do is simply start PostgreSQL using this command:

sudo /etc/init.d/postgresql start


And you're set to go!



Step 2 - Install Ruby Gems
This assumes that you have installed Ruby, Rails, and Bundler on your machine. Also, this is for Rails 3 only, so all you Rails 2 lovers, go away!

Type this command:

gem install pg


or

place this in your Gemfile:

gem 'pg'


and type "bundle install" to install the gem.


Step 3 - Configure Your Rails 3 Project
To create a new Rails project that uses the PostgreSQL database, use the following command:

rails new <project_name> -d postgresql


Replace <project_name> with the name of the project you want to create.

Alternatively, if you have an existing project that you want to switch over to PostgreSQL, just modify your config/database.yml file as follows:

config/database.yml:
# PostgreSQL v0.8.x
#   gem install pg
development:
  adapter: postgresql
  encoding: unicode
  host: localhost
  database: <project_name>_development
  pool: 5
  username: <user>
  password: <password>

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: postgresql
  encoding: unicode
  host: localhost
  database: <project_name>_test
  pool: 5
  username: <user>
  password: <password>

production:
  adapter: postgresql
  encoding: unicode
  host: localhost
  database: <project_name>_production
  pool: 5
  username: <user>
  password: <password>


Replace <project_name> with name of your project. Keep in mind that you DON'T need to have the naming like I have above; you can put whatever you really want, as long as the names are not the same. Anyway, also change <user> and <password> to the username (probably the name you chose when setting up PostgreSQL in Step 1) and the password you chose for that user.

A nice thing about the database.yml file is that you can specify even a different server (in the host: field) for each level of development, so you can have more advanced setups if you like, even though that's really all up to you. If you want to go crazy and have a server for development only, one for test only, and one just for production, you can! Not a bad idea.....

You will need to add the development and test databases above using the createdb command.

createdb <project_name>_development
createdb <project_name>_test


Replace <project_name> with the name of your Rails app, as you did in the database.yml file.

Step 4 - Install Extra Apps (Optional)There are 2 applications that help alot when you want to administer you PostgreSQL database. The first is pgadmin3. It's a GUI frontend for administrating your PostgreSQL database. I recommend this if you have install your database on your development machine, since you don't need web access and you can save on the apache server.

To install pgadmin, type the following command:

sudo apt-get install pgadmin3

The second application is called phppgadmin. It does the same stuff as pgadmin3, but it is web-based and lets you administer remote database servers easily. It's the PostgreSQL counterpart of phpmyadmin, and it seems to work great.

To install phppgadmin, use this command:

sudo apt-get install phppgadmin

Once the installation has completed, start the web server:

sudo /etc/init.d/apache2 start

Or replace "start" with "restart" if you already are running your apache2 web server. You can then access phppgadmin by going to the following web address:

http://localhost/phppgadmin

You'll need to login using your username and password, and you're set to go!