Postgres

Abstract

---

• A popular relational database
• Grab a free postgres db with Neon DB!
• Obtain the Postgres Version using SELECT version();

Resolving PostgreSQL pg_dump server version mismatch

The newer version of Postgres isn’t included in a lot of the Package Manager, you need to add the package repository in manually! You can find many online tutorials on this, here is one for APT package manager.

On MacOs, you can install with brew install postgresql@<version_number>. In order to use the new version, you have to brew unlink postgresql@<old_version>, then brew link --force postgresql@<new_version>.

Postgres Migration

---

Connection String Format

postgres://[user]:[password]@[db_hostname]/[dbname]

• Dump from one DB to another

# Export
pg_dump -O -Fc -v -d <source_database_connection_string> -f <dump_file_name>
 
# Import
pg_restore -v -d <destination_database_connection_string> <dump_file_name>

• Export data out in sql format

pg_dump -O -v -d <source_database_connection_string> > export.sql

• For more information, refer to this guide

Postgres Database Setup

-- 1) Create DB and login role
CREATE DATABASE new_db_name;
CREATE USER new_db_username WITH PASSWORD 'new_db_user_password';
 
-- 2) Give the user the right to connect/create temp schemas in the DB
GRANT ALL PRIVILEGES ON DATABASE new_db_name TO new_db_username;
-- (equivalent to CONNECT, CREATE, TEMP on the database)
 
-- 3) Create a dedicated schema owned by the app user
\c new_db_name
CREATE SCHEMA IF NOT EXISTS new_schema_name AUTHORIZATION new_db_username;
 
-- 4) Prefer setting search_path on the role (not DB-wide)
ALTER ROLE new_db_username SET search_path = new_schema_name, public;
 
-- 5) Ensure the user can use the schema and access objects
GRANT USAGE ON SCHEMA new_schema_name TO new_db_username;

Neon DB

---

• Serverless Postgres
• Billing - Neon Docs

Neon_DB CLi Setup

brew install neonctl
neonctl auth
neonctl projects list

Data migration to NeonDB

Refer to Postgres Migration, more details see Data Migration to Neon.