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
1. sudo -u postgres psql postgres
2. postgres=# \password postgres
3. Enter a new password and then confirm it.
You should now be able to login remotely (I was using pgAdmin from Windows for this).
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:
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.
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:
Type "help" for help.
Finally, all you have to do to connect is the following:
postgres=> c my_app_development
You are now connected to database "my_app_development".
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