I am just getting into PostGres and here are some rough notes for my reference.
Assuming you have a postgres admin account you want to sign in first of all and create a database
To find the command line go to search in Windows and type psql
Ensure that your postgres engine is running firstly
You should be presented with the following
There are default connections to the local host keep hitting these initially until you reach the following;
You will now need to know your password enter it here and press return
I will deal with the warning message further in the post – but a lot of people experience this message so I wanted to keep it in at present.
From my initial investigations as ever it is a good idea to restrict users to particular privileges at this level I am not being particularly refined – I would like to have a poweruser role that I can allocate to people and give this a defined password.
Signing in you can check out the roles and users as follows – on starting up a new instance you may well see something like this \du
So here I try and set up a user called general and create a role called general which I give create DB rights
I would recommend something stronger than the classic password password.
Issuing the \du command again we get to see the roles
Now we can close down and go back in but this time login as username general by altering the appropriate item when asked.
Note how the =# characters have been replaced by => this appears to denote the non superuser sign in.
To identify what username you are logged in as type \c at the prompt
My investigations suggest that the # sign denotes that you are logged into the database as superuser.
So first of all lets get rid of that annoying warning message when you log in at psql
I am running Postgres version 9.5 your version may vary but you can remove the warning by editing runpsql.bat file every version of postgres has this file and it will be located in the version equivalent directory to 9.5 for me.
C:\Program Files\PostgreSQL\9.5\scripts
Add the line
cmd.exe /c chcp 1252
as per underline and save the file
Now fire up psql as usual you should get the following
It should be noted that if you REM out the SET statements you can choose login with particular server / localhost / database / port and username presets which may be useful if you find yourself constantly going into a particular database as a particular user.
Here you see that the warning note has stopped.
It should be noted that using the general username you will NOT be able to create Databases
In order to CREATE databases you will have to be signed in with a username with sufficient privileges here I am in as postgres and I create a database called ExampleDB
You can see that on carrying out a successful command we usually see a repeat of the command.
To get a list of all databases in the instance type
\l
It can be seen that despite typing the name in capitals the database has been created in lower case this seems to be a feature of commandline. If you desperately want capitals you may need to go to pgadmin tool.
I think I’ll end it there for now.