Category Archives: PostGreSQL

Setup PostgreSQL on an Azure VM

Leave a reply

Hi everyone,

I’m currently testing out a small linux vm with PostgreSQL on Azure. Just sharing the steps for future reference and hopefully it’ll be able to help you out as well.

# Setup PostgreSQL on Azure VM

## Run updates
sudo apt-get update
sudo apt-get install postgresql

## Setup postgresql.conf
1. sudo vim /etc/postgresql/10/main/postgresql.conf
2. Add or uncomment `listen_addresses = '*'`

## Update pg_hba.conf
1. sudo nano /etc/postgresql/10/main/pg_hba.conf
2. Add the following line `host    all             all             151.XXX.XXX.XXX/32        md5` *\*where the ip is your public ip*
3. Restart pgsql `invoke-rc.d postgresql restart`

## Setup a PostgreSQL user
https://serverfault.com/a/248162/148152  

1. sudo -u postgres psql postgres
2. postgres=# \password postgres
3. Enter a new password and then confirm it.

## Done
You should now be able to login remotely (I was using pgAdmin from Windows for this).

Good luck!

ERROR: column “category” does not exist

Leave a reply

I’ve recently switched from SQLite3 to PostGreSQL and came across the following error this morning:

my_app_development=> select category from codes;
ERROR:  column "category" does not exist
LINE 1: select category from codes;

The following post solved the issues in one sentence:

http://archives.postgresql.org/pgsql-novice/2007-01/msg00032.php

Yes. In postgres, unquoted column and table names are converted to lower case.

The user also added that when using PostGreSql you should either be always, or never using quotes around column names.

Connect to Database – PostGreSQL

Leave a reply

This is just a quick post on how to connect to particular database via the console in PostGreSQL. First, make sure you’ve logged in – just swap my_app for your role name:

chris@chris-VirtualBox:~/site$ psql postgres my_app

Password for user site: 
psql (8.4.10)
Type "help" for help.

Finally, all you have to do to connect is the following:

postgres=> c my_app_development

psql (8.4.10)
You are now connected to database "my_app_development".
my_app =>

If you’re having trouble working out what database name you’re trying to connect to you can view a list of all the current database with the following command:

my_app => l

 List of databases
       Name       |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
------------------+----------+----------+-------------+-------------+-----------------------
 postgres         | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 my_app_development | site     | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 my_app_production  | site     | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 my_app_test        | site     | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 template0        | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 |

my_app => q -- q to exit database list