Install RazorSQL on Ubuntu 12.04 LTS x64

Introduction

RazorSQL is a GUI tool for working with Postgresql.

Install RazorSQL

First, create the razorsql directory.

mkdir ~/Downloads/razorsql

Download RazorSQL into the ~/Downloads/navicat directory.

  1. Open a web browser to http://www.razorsql.com/download_linux.html.
  2. Click Download next to Linux (64-bit).

After the download completes, open a command prompt and enter the following commands. I have assumed that you downloaded the .zip file to the Downloads/razorsql directory.

cd ~/Downloads/razorsql
unzip razorsql5_6_4_linux_x64.zip
mv razorsql ~/bin/
cd ~/bin/razorsql
chmod 755 razorsql.sh
./razorsql.sh

Connect to a Database

The following steps are performed in the RazorSQL GUI.

  • From the menu, select Connections, click Add Connection Profil.
  • In the Connection Wizard, select Postgresql, click Continue.
    Add Connection Profile 01
  • In the 2nd screen, enter the information shown below.
    Add Connection Profile 02
  • Click Connect.

Enable TCP/IP Connections to Postgresql 9.1

Introduction

The default installation of Postgresql does not allow TCP/IP connections, which also means JDBC connections wont’ work.

Enable TCP/IP Connections

cd /etc/postgresql/9.1/main

Let’s make a backup of the original config file.

sudo cp postgresql.conf postgresql.conf.org
sudo vi postgresql.conf

Find the line that contains

#listen_addresses = 'localhost'

and change it to:

listen_addresses = 'localhost'

If you want to allow remote connections, then you need to set listen_address to something like (assuming your machine’s IP Address is 192.168.1.2):
listen_addresses = ‘127.0.0.1 192.168.1.2’

You will also need to update the firewall to allow connections to this machine.

Restart the Postgresql Server

sudo service postgresql restart

Test the TCP/IP Connection

psql -h 127.0.0.1 -p 5433 -U postgres -W

Initial Configuration of Postgresql on Ubuntu 12.04 LTS

Introduction

After installing Postgresql on Ubuntu, you will need to complete a few basic setup steps.

Set the Password for the postgres User

The first step is to update the password for the postgres user.

sudo -u postgres psql postgres

Then enter the following command at the postgres=# prompt.

\password postgres

Create a Database

If you exited the postgres=# prompt, then enter the following command to connect to Postgresql. However, if your prompt still looks like postgres=#, then do not enter the psql command below.

sudo -u postgres psql postgres

Enter the following command at the postgres=# prompt.

First we’ll list all of the databases, which should be the following: postgres, template0, and template1.

\l
CREATE DATABASE practicedb;
\l

Now you should see the practicedb database.

Press Ctrl + D to exit the Postgres prompt.

Install Postgresql on Ubuntu 12.04 LTS

Introduction

Postgresql is an open source object relational database. It is often thought of as an alternative to MySQL.

In this post I’ll provide the steps required to install Postgresql on a developer laptop, assuming that work will be done in both SQL and Python.

You can learn more at:

Installation

Open a command prompt and enter the following commands.

sudo apt-get install postgresql postgresql-common postgresql-contrib \
postgresql-client postgresql-client-common \
pgsnap pgadmin3 pgpool2 ptop pgtune pgloader pgagent \
python-pygresql postgresql-plpython-9.1 python-psycopg2

You’ll see a list of additional packages that will be installed by default.

The following extra packages will be installed:
libossp-uuid16 libpgpool0 libpq5 libwxbase2.8-0 libwxgtk2.8-0 pgadmin3-data php5-cli php5-common php5-pgsql postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1 python-support

Installed Packages

Each of the installed packages are described below.

postgresql
This is the core Postgresql object relational database server.

postgresql-common
Add the ability to setup a cluster of Postgresql database servers.

postgresql-client
Command line client for interacting with a Postgresql database, including the psql command.

postgresql-client-common
Allows multiple clients to be installed as part of a Postgresql cluster.

pgsnap
Generates a Postgresql performance report in HTML format.

pgadmin3
GUI tool to work with Postgresql. Personally, I prefer some of the commercial tools, such as Navicat on Linux or EMS SQL Manager on Windows.

pgpool2
Middleware between a Postgresql client and a Postgresql server that provides connection pooling, replication and load balancing, among other things.

ptop
CLI based performance monitoring tool that’s analogous to the Linux psql command

pgtune
Automatically tunes the Postgresql configuration file postgresql.conf based on the system’s hardware.

pgloader
Utility for loading flat files and CSV files into a Postgresql table.

pgagent
Job scheduler for Postgresql.

python-pygresql
Python module that allows you to query a Postgresql database from a python script. Basically, this is for python developers who need to query Postgresql.

postgresql-plpython-9.1
Allows SQL developers to extend their SQL script by writing procedural functions in python.

python-psycopg2
Similar to python-pygresql, except that python-psycopg2 is designed for heavily threaded python scripts that create and destroy a large number of cursors, and execute a high volume of INSERTs and UPDATEs.