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!

Tuesday, April 12, 2011

Some Random Thoughts

So this week I'm in Pompano Beach working at the office. I've been developing new software for the company I work for, Alarms GPS. I just finished eating a lunch of rice & beans, chicken, and lettuce. Very good stuff. Right now I'm sitting at my desk waiting anxiously to receive an email from Movistar, a cell service company down in Mexico, to finish a VPN configuration that we've been working on for 3 weeks. Let's just say that it's somewhat frustrating to spend forever doing something that could really take one afternoon....but anyway....

I've been using Google Chrome lately, and I like it because it's fast, it has great themes, and it has lots of cool extensions. Not as many as Firefox, but who really cares, because I only use 2: Xmarks and AdBlock plus. You should check them out, they are really worth it! Anyway, I'm using Ariel to write this blog post, and I've got to say, this netbook really has got a great feeling keyboard! I'm running Ubuntu 10.10 32-bit, which makes her really fast and speedy. Check out a snapshot of my desktop (yes, I have a wallpaper for openSUSE, but come on, it looks cool!!):



I used a cool app called Shutter (only for Linux) to create the cool 3D reflect effect...

Otherwise, I'm trying to get these people to take me out to test our car tracker/alarm now. The car alarm is just a device that you plug into your vehicle that has a remote control (to lock and unlock doors or start the engine) which also tracks where it is which you can see on our tracking webpage (it uses Google Maps, which is cool). I think the alarm pretty much ready, it just needs a final test run to make sure it works as advertised, and that I didn't mess up anything important. I really need to get my own car (and not to mention, my license) so I can just go and test it the way I want without having to worry about anything.....

Alrighty, time to get back.....so long!

Jesse L. Zamora

Sunday, April 3, 2011

Firewalls, Me, And My Netbook Ariel

So lately I've been configuring firewalls. What ever for? Well, the company for which I work (Alarms GPS) has a network of servers that need to be protected from the outside. So, I configured the entire network for the entire 6 or so servers we have using a Fedora 11 server with Shorewall. Most of you don't really care, so you don't have to keep reading. Anyway, at first it was difficult trying to figure out  how to get the configuration started, but eventually I got it working and was able to also configure an IPSEC VPN using the Linux ipsec-tools (Racoon and setkey) with Wyless (a T-Mobile service provider). Either way, this past week in our brand new office in Fort Lauderdale, we setup a local firewall not only for security, but also to run a proxy server that allows us to block certain webpages, such as YouTube, Facebook, or the notorious Windows Live Messenger. The configuration was simple, and I got the proxy server running, but I haven't figured out how to get it to block sites yet. So I'll need to go back down there and finish it.

Right now I'm blogging from my new netbook (named Ariel). I'm waiting for the peeps to get off the Internet so I can install our own internet blocking system so we can limit the websites we go to, and even specify what times we can go on the Internet (e.g., we could block the Internet after 10:00 PM so people won't be surfing at midnight, etc). One appealing thing is that while a proxy server can block information, it can also help to speed up the Internet connection. Yes, really, it does speed up your Internet a whole lot. What it does is that it saves a "cache" of webpages on the server so that the next time you access that page, it can quickly provide the basic elements in record time, and the page loads instantly. So that's another reason to have a firewall here at the house, since we don't have the fastest Internet all the time....

When everybody is off the computers, hacking time begins! They are taking way too long, and my battery is getting lower and lower, and still I haven't started to get the thing setup. Seriously, is there no order in this place??? Either way, I'm going to go get my power adaptor to get the battery charging before I ruin my battery pack.

If you've read this far, I commend you for your valor. I assume you don't understand most of what I have written in the above paragraphs, but if you did (if you're a Linux geek or something), then great! Well, I've got to stop blogging now. So, till next time!

Jesse L. Zamora

Saturday, April 2, 2011

New Netbook

Yay! I just got my new ASUS EeePC Seashell Series netbook! I have given her the name "Ariel" since I like that name...and also, because she's just hot!!!
 

Here Ariel's specs:

Size: 12.1 in.
CPU: AMD Athlon Neo II 1.7 GHz
Memory: 2GB DDR3
Graphics: AMD Radeon HD 4250
Hard Drive: 320GB SATA II
OS: Windows 7 Home Premium 32-bit
Battery Life: Up to 6 hours!

I'm installing Ubuntu 10.10 Netbook Edition next to Windows 7 in order to have a dual boot system. This is totally aweseme! Why do I want to keep Windows? Well, to compile the Qt4 apps I work on to work well on other Windows 7 machines!!

I'm also installing Ubuntu 10.10 Netbook Edition as my main installation. I'll install the Qt4 SDK and add stuff for development of PHP code, as well as add GIMP and Inkscape for my image editing and creation needs.

All I have to say about Ariel is that she is totally awesome. I mean, for $300 (without shipping), I get a hot netbook that is almost twice as powerful as my previous netbook for the same price. And it's got a bigger screen. This is so awesome!!!

Sorry Thumbelina (my previous netbook), but I've found someone else....

Saturday, January 22, 2011

A stark realization

So I've just returned from this awesome party. It wasn't exactly a rager or anything, but it was really fun! But, while spending time my friends and hanging out, I've realised something. If you don't already know, I spend countless hours in front of the computer working on apps and websites and stuff. Anyway, it tends to be really addicting....kind of like Facebook is, but without the "social" aspect of it. I very rarely go out, even to the store, and sometimes I don't even step out the front door or get any sunlight. So what I've realised is that I need to get out more! And I mean, a lot more. I don't know if anyone really cares, but I once heard this: "There will be always be new jobs, concerts, or movies. But there is only one chance to spend time with your loved ones..."

The way I see it, well, it's a good thing to work hard and keep yourself busy. But, neglecting to spend time with your family and even your friends can be the worst thing you could possibly do. There will always be more work to be done; but your loved ones will not always be there to hang out or spend time with. Upon my realisation of this, I have made a resolution. While continuing to work hard, I will also just hang out with people. Perhaps do something fun...anything that's not what I do every single day day after day. I am slowly beginning to lose any social skills that I may have left...and that is very saddening.

I'm sure this post has been quite a boring dissertation...but I thought I'd just out and say it, since after all I have been quite a recluse....

Thanks for reading,
Jesse L. Zamora

Monday, November 1, 2010

A Glimpse Into the Dark and Strange World of the Zamora Household

Last week I was in Pompano Beach on a business trip with my Dad. You'd better believe that it was pretty stressful to have to act all professional like without any random outbursts of pure creativity. So what happens? Well.....maybe I'll just show you.





I'm not sure exactly what to say about these pictures. Perhaps some of you may need therapy when you're older. I don't know. But this is a direct result of having too much time on our hands, a new webcam, and probably even serious mental problems. And now you know the truth...

Alrighty everyone, have a good one!

Disclaimer: No innocent minds were damaged in the making of this post. If you suffer boredom, temporary insanity, feelings of pity, shock, dramatic reactions, or serious twitching of the eye or other muscles, try calling you psychiatrist and/or growing up. XtremeK Forever! and associates do not claim responsibility for any damage done to children and/or adult minds as a result of viewing this post.