- Opening command Prompt (psql)
- Creating Database
- View all databases
- Login to newly created database
- Renaming Database
- Create table in new database
- Create Data in tables
- View Table(s) & Data
- Search Data in database tables
- Search and update Data.
- Connecting Command prompt to postgres
- Configuration Files
- Duplicate Database
- Starting Postgres Server
- Create & View all user
- View all databases in database cluster
- Database Drivers
Opening Command Prompt (psql)
When you download postgres you will get acces to “PSQL” (Postgres command line interface) by default. Start by doing a search in you computer from “PSQL”.
When you open up the CMD you will imidiaelyt be prompted to login and connec to database
Once your are logged in, in the same psql command prompt you can the command “create database” with the database name to create a new database
View all Databases
To view all databases just type “\l” or “\list”in the command prompt and you will see all databases on that server including the new one we just created.
Connect to another databases
To connect to your new databases or any other on our server run the command “\connect dbname;”
To rename a database run the following command: “ALTER DATABASE oldname RENAME TO newname”
Create Table in command prompt (PSQL)
To create a table use the “CREATE TABLE” command. Because we are working line by line we will need to run the command with the table name and leave the command open with a curly brace like in the picture below. This will leave our command open to add a row. If we were adding multiple rows we would to to add a comma after. finally to close the command just add “);” and your table will be added. To check the tables in your database you can run the “\dt” command, also in the image below
You can view you table structure by running “\d table_name”
From my experience when i ran the command above the table was added but i had no ability to add data in my PgAdmin account. The solution to this was to add a column that represents the primary key.
We will discuss adding data in the next section so i will address how to do this from the command line
Lets start by adding another table called “Products”
To create a primary key add a column with the value of primary key.
Before we add data lets add another column to our products table.
ALTER TABLE table_name
ADD COLUMN new_column_name data_type;
We can put this command into one like so:
ALTER TABLE products ADD COLUMN price text;
Finally lets add some data.
To do so we will user the INSERT command. Specify the table name, columns you will be adding to and add the values in order. Each row needs to be a list of values.
The only issue with what we have bellow is we don’t want to have to add the “ID” field, this is good practice should auto increment. I will add in how to do this once i solve it.
View Data in table
To view data in a table simple run the SELECT command. “*” will select all data so we can see everything.
SELECT* FROM products;
Search and update data
Lets say we need to make an update but dont want to go through our database looking for each item and updating it. I will use a small example but this can be scaled to larger databases where it makes more sense to use this method.
We will query our products table and Re=price our peanuts. In this example it will only update one item but the concept would apply if there were many.
The price of any product with the name peanut has been updated to $9.
Connecting Command prompt to postgres
There are several ways to duplicate a database so i will focus on the one that works best for what i needed. My goal was to backup a database i had on a live project hosted on AWS to my local computer for safe keeping.
For this we will use pg_dump & pg_restore. Our first example will be backing up a local database and uploading it to a new database.
I am new to postgres so my first issue was figuring out where to run the pg_dump command from. My issue was that i kept trying to run it for “psql”, then when i figured out you need to run it from your computers command prompt i couldn’t find the file path to postgres. I’ll address how to find the file path first just in case your running into the same issue.
I use PgAdmin as my GUI so to find the file path i looked in my dashboard:
- Open PgAdmin
- File –> Preferences
- Paths –> Bionary Paths
- PostgreSQL Binary Path
There you will find your file path which you can now add to your command prompt to access your postgres server
These are the 3 main configuration files that control a postgres server:
- postgresql.conf – Controls General settings
- pg_hba.conf – Controls access to server
- pg_ident.conf –
To find the location/file path to all these files you can run the following command in psql:
SELECT name, setting FROM pg_settings WHERE category = ‘File Locations’;
Duplicating Database from one another server
I will be honest i just learned this so i this is a way i made it work. I try to add more examples and a breakdown of how this saves the file:
pg_dump -U postgres dennisivanovdb > c:\users\jason\desktop\dennisivanovdbbackup1.sql
The password you will get promted for is the password you set up for your postgress account
No lets add this data into a new database
psql -U postgres -d newdata -f C:\users\jason\desktop\dennisivanovdbbackup1.sql
This will prompt you for you username password. If all worked your data should have transferred.
Lets try pulling a database from our AWS account
I followed the steps on this page and was able to pull down my live database from AWS
This is the command i ran to copy the database
Lets try restoring the database to a local database
This command actually imported all my data for me but with permissions error. ]
pg_restore -U postgres -d lims10backup C:\users\jason\desktop\livedb.sql
Starting Postgres Server
Before you can access you database you must first start your database server.
Followed this link: https://stackoverflow.com/questions/14948406/how-to-start-postgres-server
Create & View Users/Roles
Before we create our new user lets view all the users in our database cluster. We will user psql by logging into one of our databases.
To view all users run the following command:
SELECT rolname FROM pg_roles;
Now to create a new user to the database cluster run the command:
CREATE ROLE newusername LOGIN PASSWORD ‘password’
This will add a new user wich you can view by running the command above. We can set our users permissions right away or add them later.
View all databases in database cluster
To view all the databases in a cluster we will login to psql just like we did for our users. then we can run the following command:
SELECT datname FROM pg_database;
To view a database from live project you would need to just specify your host. I use AWS so i would just provide my database instance.
Database drivers help our applications interact with our databases & other databases. There are many database drivers out here. These are my preferred two when working with python.