Setting up Local PostgreSQL Database

Last month, I create a database on Vercel Postgres to store the data in a wildlife conservation webiste. However, the free version has monthly limitations on data storage and transfers. To avoid wasting online resources, I decide to set up a local PostgreSQL environment to facilitate the debugging process for the upcoming web application development.
To install PostgreSQL server, update your computer's local package cache with the latest set of packages. Afterwards, install the postgresql
package:
sudo apt update
sudo apt install postgresql
By default, PostgreSQL is configured to use peer authentication, which allows users to log in if their operating system user name matches a PostgreSQL internal name.
The installation process created an operating system user called postgres
to match the postgres
database administrative account.
To log into PostgreSQL with the psql
client, use sudo
to run the command as the postgres user:
sudo -u postgres psql
ALTER USER postgres PASSWORD 'yourpassword';
-- ALTER ROLE
After setting password for user postgres
, you can connect to postgres with URI:
psql 'postgresql://postgres:yourpassword@localhost:5432/postgres?connect_timeout=10&sslmode=require'
For more details, you can refer to:
The CSV file, animals.csv
, containing the data given below:
less animals.csv
# 1,Mammalia,Primates,Lorisidae,Nycticebus bengalensis,Bengal slow loris,蜂猴,I,EN,false,false,I
# 2,Mammalia,Primates,Lorisidae,Nycticebus pygmaeus,Pygmy slow loris,倭蜂猴,I,EN,false,false,I
# 3,Mammalia,Primates,Cercopithecidae,Macaca arctoides,Stump-tailed macaque,短尾猴,II,VU,false,false,
# ...
In this table, the following columns should be present:
Create the animals
table with the above schema with the following SQL:
CREATE TABLE IF NOT EXISTS animals (
id serial PRIMARY KEY,
class VARCHAR ( 50 ) NOT NULL,
"order" VARCHAR ( 50 ) NOT NULL,
family VARCHAR ( 50 ) NOT NULL,
scientific_name VARCHAR ( 50 ) UNIQUE NOT NULL,
common_name VARCHAR ( 50 ) NOT NULL,
chinese_name VARCHAR ( 50 ) NOT NULL,
protection_class VARCHAR ( 2 ) NOT NULL,
conservation_status CHAR ( 2 ) NOT NULL DEFAULT 'DD',
aquatic BOOLEAN NOT NULL DEFAULT FALSE,
endemic BOOLEAN NOT NULL DEFAULT FALSE,
cites VARCHAR(50)
);
-- CREATE TABLE
Now you can easily import CSV to PostgreSQL via using the COPY
Command:
\COPY animals
FROM '/path/xxx/animals.csv'
WITH csv;
-- COPY 186
and show table contnet:
\d
-- List of relations
-- Schema | Name | Type | Owner
-- --------+----------------+----------+----------
-- public | animals | table | postgres
-- public | animals_id_seq | sequence | postgres
-- (2 rows)
table animals;
-- id | class | order | family | scientific_name | common_name | chinese_name | protection_class | conservation_status | aquatic | endemic | cites
-- -----+----------+----------------+-----------------+------------------------------+---------------------------------+------------------+------------------+---------------------+---------+---------+-----------
-- 1 | Mammalia | Primates | Lorisidae | Nycticebus bengalensis | Bengal slow loris | 蜂猴 | I | EN | f | f | I