Posts

postgres unique id column management

 UUID function in postgres: UUID is a unique identifier used in primary key columns as an alternative to the sequences. You can create a table column with UUID datatype and auto generate the values for the column to ensure uniqueness. See the example below. Step 1: Create the extension as below to use the uuid generator function. mytestdb=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION Step 2: Create table with uuid column mytestdb=# create table ytable (id uuid, name varchar(20)); CREATE TABLE mytestdb=# select * from ytable;  id | name  ----+------ (0 rows) Step 3: Insert new values using the uuid generator during insert: mytestdb=# insert into ytable values (uuid_generate_v4(), 'aaa'); INSERT 0 1 mytestdb=# insert into ytable values (uuid_generate_v4(), 'aaa'); INSERT 0 1 mytestdb=# insert into ytable values (uuid_generate_v4(), 'aaa'); INSERT 0 1 mytestdb=# insert into ytable values (uuid_generate_v4(), 'aaa'); INSERT 0 1 myt...
Post a Comment
Read more

PostgreSQL Tools

Postgres has few tools that help to query or do maintenance tasks: 1. psql   2. pg_ctl 3. pg_lsclusters 4. pg_ctlcluster Psql is an terminal based front-end interface for PostgreSQL for querying anything about Postgres server and its databases. Few examples are here. psql   commands \? : show all psql commands. \h sql-command : show syntax on SQL command. \c dbname [username] : Connect to database, with an optional username (or  \connect ). Display Commands: You can append  +  to show more details. \l : List all database (or  \list ). \d : Display all tables, indexes, views, and sequences. \dt : Display all tables. \di : Display all indexes. \dv : Display all views. \ds : Display all sequences. \dT : Display all types. \dS : Display all system tables. \du : Display all users. \x auto|on|off : Toggle|On|Off expanded output mode. For automations this below may be helpful as this is executed from Linux command line and the output can be redirected to...
Post a Comment
Read more

PostgreSQL for Oracle DBAs

 As an Oracle DBA in my past life, I have thought about giving some understanding and relatable definitions of PostgreSQL compared to Oracle Databases. I hope this helps to understand how these two relational databases are comparable for Oracle DBAs to quickly adapt to the new DB server technology. I will not be giving detailed explanations in this article. For more details on each topic, please refer to the postgreSQL documentation. Database architecture compared: Oracle : SGA: Fixed physical memory size allocated to each server out of available RAM Postgres : Total memory is allocated to postgres server running multiple databases within. Oracle : Buffer Pool Postgres : Share Buffers Oracle : Redo log buffers / Redo log files Postgres : WAL buffers / Wal files Oracle : Archivelog Files Postgres : Archivelog Files Oracle : UNDO files are used to hold original copy of the dirty pages for transaction isolation Postgres : WAL handles this. Oracle  : SMON process is responsible f...
Post a Comment
Read more

Move postgreSQL server data to new Folder Location

 Move the entire postgres server files from default location to a custom location on the server A word of Caution: This procedure involves a database shutdown and restart needed.  Please make sure you have  change request raised if you are working on production database and get it approved before scheduling this change.  You must notify the application team about the change as well and set the down time expectations. It is better to maintain the standards in the naming convention for the common scripts to monitor and support any activities. Pre-Steps: Create the required folder structure and modify the ownership and permissions on the folder structure similar to the below steps           $ sudo su - root           $ mkdir -p /myorg/postgres/           $ chown -R postgres:postgres /myorg/postgres           $ chown -R 755 /myorg/postgres Note:  D...
Post a Comment
Read more

Tables, Indexes and Triggers in Postgresql

  List of table level commands and syntaxes in Postgres: Sample Create table command with constraints: mytestdb#  create table mytable(  empno integer primary key, firstname varchar(50), lastname varchar(50) ); Describe the table structure: mytestdb#  \d mytable    << describes table showing all columns, datatypes, and constraints select table_schema||'.'||table_name, column_name, data_type, is_nullable from information_schema.columns where table_schema='payments' and table_name='customer' ; Create table with all constraints and serial no columns etc., mytestdb=# create table customer (      customer_id serial primary key,      firstname varchar(50) not null,      lastname varchar(50) not null,      email varchar(50) unique not null,      address1 varchar(50) not null,      address2 varchar(50) not null,      city varchar(30) not null,   ...
Post a Comment
Read more

Basic Administration Commands in PostgreSQL

  Lets have a list of basic administrator help commands: Connect to server using psql $ sudo -u postgres psql <existing database name you want to connect> sudo -u postgres psql mytestdb mytestdb#    << you are ready now Show list of databases in the server: mytestdb#  \l   or \list Show the Server version: mytestdb#  select version(); Show current database name you are connected to: mytestdb#  select current_database(); Show current user you are logged in as: mytestdb#  select user; Show list of tables in all databases; mytestdb# \d <shows schema name, object name, type and owner> Show the list of tables in the current database: mytestdb# \dt    or   \dt+ Show the columns of a particular table: mytestdb#  \d mytablename; show the schemas under a database: mytestdb# \connect <databasename> mytestdb#  <<use either of the below sqls >> select schema_name from information_schema...
Post a Comment
Read more