How to check the status, stop and start a postgreSQL server

The postgreSQL server comes with a wrapper called pg_ctl which will simplify some admin tasks for you.

Check if the variable PGDATA is set:

[postgres@myserver ~]$ echo $PGDATA
/pgdata/data

Check status:

[postgres@myserver ~]$ pg_ctl status
pg_ctl: server is running (PID: 27870)
/usr/pgsql-11/bin/postgres "-D" "/pgdata/data"

If the PGDATA variable is not set in your operating system, you need to specify the D-flag, like this:

[postgres@myserver ~]$ pg_ctl status -D /pgdata/data
pg_ctl: server is running (PID: 27870)
/usr/pgsql-11/bin/postgres "-D" "/pgdata/data"

Check if the database accepts incoming requests:

[postgres@myserver ~]$  pg_isready -d testdb01
/var/run/postgresql:5432 - accepting connections

Stop:

[postgres@myserver ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

# stop immediate - similar to Oracles "shutdown abort". Instance recovery needed
pg_ctl -D $PGDATA stop -m immediate
waiting for server to shut down.... done
server stopped

# stop fast - similar to Oracles "shutdown immediate". Rollback of uncommited transactions, shutdown gracefully. No instance recovery needed
pg_ctl -D $PGDATA stop -m fast
waiting for server to shut down.... done
server stopped

# stop smart - similar to Oracles "shutdown normal". Waits for all transactions to complete. No instance recovery needed. May take time to complete.
pg_ctl -D $PGDATA stop -m smart
waiting for server to shut down.... done
server stopped

Start:

[postgres@myserver ~]$ pg_ctl start
waiting for server to start....2019-01-30 10:35:04 CET LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-01-30 10:35:04 CET LOG:  listening on IPv6 address "::", port 5432
2019-01-30 10:35:04 CET LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-01-30 10:35:04 CET LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-01-30 10:35:04 CET LOG:  redirecting log output to logging collector process
2019-01-30 10:35:04 CET HINT:  Future log output will appear in directory "log".
 done
server started

RHEL7 or later

If your postgres server is running on RHEL7 or newer, you should use systemctl to control the service:

systemctl start postgresql-11
systemctl stop postgresql-11
systemctl restart postgresql-11
systemctl status postgresql-11

Example output(abbreviated) from the status command:

● postgresql-11.service - PostgreSQL 11 database server
   Loaded: loaded (/etc/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2021-01-08 02:20:51 CET; 1 months 10 days ago
     Docs: https://www.postgresql.org/docs/11/static/
 Main PID: 1910 (postmaster)
   CGroup: /system.slice/postgresql-11.service
           ├─ 1910 /usr/pgsql-11/bin/postmaster -D /postgres/data
           ├─ 2047 postgres: logger
           ├─ 2390 postgres: checkpointer
           ├─ 2392 postgres: background writer
           ├─ 2394 postgres: walwriter
           ├─ 2395 postgres: autovacuum launcher
           ├─ 2396 postgres: archiver   last was 00000001000002360000009A
           ├─ 2397 postgres: stats collector
           ├─ 2398 postgres: logical replication launcher
           ├─ 2766 postgres: postgres_exporter postgres localhost(60360) idle
           ├─ 3061 postgres: postgres_exporter postgres localhost(44202) idle
           ├─48217 postgres: postgres postgres [local] idle

If the postgres server is down, and you need to check the current version installed, you can use either of these methods:

which psql
/sw/postgres/pgsql-13/bin/psql

cat /u02/pgdata/datafiles/PG_VERSION
13

cat /etc/PG_VERSION
13.ansible

The difference between REDO and UNDO

UNDO	REDO
Used during normal database operations	Used during instance and media recovery only
Contains changed column values	Contains "change vectors", a description of a change made to a single block
Stored inside the database, in a separate tablespace	Stored outside the database, in redo log files
Can be utilized for many purposes	Has one purpose only

The redo log files has one and only one purpose in life: instance recovery.
The undo tablespace(s) has one primary purpose in life: to support read-consistency. However, unlike redo, UNDO data can also be utilized for many other purposes, like Oracles flashback technologies.

Oracle 12.2 Documentation about the redo logs can be found here

How to avoid ORA-00947 "not enough values" when performing an ITAS operation

If you are inserting rows from a table using an ITAS (Insert Tables As Select) statement, and the table being written to has a different layout than the table being selected from, you may struggle with getting your INSERT statement to succeed.

For example, let's say that the interim_table below has a virtual column defined on it, that the original table does not have. When attempting to execute your ITAS statement, you may see the error

ORA-00947: not enough values

To avoid this, enclose the the column listing in parenthesis, while omitting it for the SELECT statement constituting the VALUES-list, like this:

insert into interim_table
( 
  tq_id
  ,ename
  ,setting
)
  SELECT
    f.tq_id,
    f.ename,
    f.setting
  FROM original_table f JOIN interim_table wf
  ON wf.tq_id = f.tq_id;

How to enable DML logging

DML logging can be used as a workaround if you have a large number of rows to process, and you expect that some of these will fail to be inserted correctly due to data quality problems. In other words, some rows may violate one or more constraints on the receiving table.

Here is a short demonstration that I have recently tested as a workaround for a client.

I have a table called MYTABLE, which looks like this:

 Name                   Null?    Type
 --------------------- -------- -------------------
 ORDER_ID                        NOT NULL NUMBER
 ORG_ORDERID                     NUMBER
 VALUE_ID_TYPE                   VARCHAR2(20)
 COL_PREFIX                      VARCHAR2(80)
 COL_NAME                        VARCHAR2(500)
 COL_SUFFIX                      VARCHAR2(20)

There is a unique constraint on the table:

--create the index
CREATE UNIQUE INDEX C_COL_UIX ON MYTABLE
(ORG_ORDERID, COL_PREFIX, COL_NAME, COL_SUFFIX)
TABLESPACE USERS;

-- create the constraint, using index created above
ALTER TABLE MYTABLE ADD (
  CONSTRAINT C_COL_CONS
  UNIQUE (ORG_ORDERID, COL_PREFIX, COL_NAME, COL_SUFFIX)
  USING INDEX C_COL_UIX);

The actual DML logging is performed by adding the statement

LOG ERRORS INTO error_table ('INSERT') REJECT LIMIT UNLIMITED;

at the end of the INSERT statement.

Before we can do this we need to create the error log table:

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'MYTABLE');
END;
/

The statement above will create a table called ERR$_MYTABLE, where rejected rows will be inserted into.

For this test, I will now verify that the values I intend to use, actually exist in the table:

select count(*)
from mytable
where org_orderid = 122
and col_prefix = 'A'
and col_name = 'B'
and col_suffix = 'X'
;

  COUNT(*)
----------
         1

Any attempt to insert a row into the table with the same values as in the select statement above (122, A, B, X), will violate constraint C_COL_CONS, and logged in the table ERR$_MYTABLE.

Let's execute an INSERT statement that will violate the constraint:

INSERT
INTO mytable (order_id, col_prefix, col_name, col_suffix, col_type)
VALUES(122, 'A', 'B', 'X', 'D')
LOG ERRORS INTO ERR$_MYTABLE ('INSERT') REJECT LIMIT UNLIMITED;

As expected, I get the following error:

ORA-00001: unique constraint (C_COL_CONS) violated

Let's check the log table:

SELECT * FROM ERR$_MYTABLE;

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	ORDER_ID	ORG_ORDERID	VALUE_ID_TYPE	COL_PREFIX	COL_NAME	COL_SUFFIX
1	ORA-00001: unique constraint (C_COL_CONS; violated		I	INSERT	2850841	122	D	A	B	X

If you execute a batchjob with thousands of rows, the offending rows will be caught so they can be corrected later. The non-offending rows will be neatly inserted where they belong.

What types of materialized views can be created in an Oracle database?

There are three types of Materialized Views that can be created in an Oracle database:

1. Materialized Views with Aggregates

* The most commonly used materialized view, particularly in Datawarehouse databases.

Example:

CREATE MATERIALIZED VIEW SEQ_AGGR_INFO 
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
select RDATE, count(SEQNO) as antall, max(SEQNO) as makssekvens
from DATE_TO_SEQNO
group by dato
order by 1, 2;

Requirements:

* The SELECT-list must contain all of the GROUP BY columns (if present)
* There must be a COUNT(*) and a COUNT(column) on any aggregated columns.
* A materialized view logs must be present on all tables referenced in the query that defines the materialized view.
* Valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX.

2. Materialized Views containing only joins

* Materialized views contain only joins and no aggregates

Example:

CREATE MATERIALIZED VIEW active_data
TABLESPACE data1
CACHE
REFRESH FORCE ON COMMIT
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT 
    act.q_id, 
    act.doc_type, 
    act.next_q_id, 
    act.prev_q_id,
    act.created, 
    act.created_by, 
    act.last_modified, 
    act.last_modified_by, 
    act.contents, 
    act.accum_value, 
    act.accum_total
FROM active_data act JOIN archived_data arc
ON arc.q_id = act.q_id;

An advantage with these types of materialized views is that expensive joins are precalculated.
Only REFRESH ON COMMIT or REFRESH ON DEMAND is legal with mviews containing only joins. When ON COMMIT is used, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table.


3. Nested Materialized Views

* A nested materialized view is a materialized view whose definition is based on another materialized view.

I have no personal experience using this type of materialized view, but Oracle has supplied some examples if you follow the link above.

Sources: Oracle 12.2 Data Warehousing Guide

Some simple timestamp expressions

Here is a simple template for how to add hours, minutes and seconds using Oracle timestamp expressions:

select sysdate, sysdate+1/24 "+ 1hr",  sysdate+1/24/60 "+ 1 min",  sysdate+1/24/60/60 "+ 1 sec", sysdate+10/24/60/60 "+ 10 sec"
from dual;

SYSDATE	+ 1hr	+ 1 min	+ 1 sec	+ 10 sec
17.01.2019 13:34:38	17.01.2019 14:34:38	17.01.2019 13:35:38	17.01.2019 13:34:39	17.01.2019 13:34:48

What is a "fast refresh" of materialized views?

A FAST REFRESH of a materialized view applies only changed data to the materialized view. This way you don't have to rebuild the materialized view every time data in the base tables' change. Oracle keeps a log of changed data in a different object called a materialized view log.

To indicate a FAST REFRESH, use the following syntax:

CREATE MATERIALIZED VIEW SEQ_AGGR_INFO
REFRESH FAST ON COMMIT | ON DEMAND | ON STATEMENT | START WITH SYSDATE NEXT SYSDATE+1/24/60
ENABLE QUERY REWRITE
AS
select RDATE, count(SEQNO) as antall, max(SEQNO) as makssekvens
from DATE_TO_SEQNO
group by dato
order by 1, 2;

ON COMMIT instructs oracle to refresh the materialized view whenever a transaction against the master table commits.

ON DEMAND instructs oracle that the materialized view should not be refreshed unless the user manually executes one of the DBMS_MVIEW refresh procedures.

ON STATEMENT instructs oracle to automatically refresh refresh the materialized view every time a DML operation is performed on any of the materialized view's base tables.

START WITH specifies a datetime expression for the first automatic refresh time. NEXT indicates the interval between automatic refreshes

General restrictions for FAST REFRESH are listed here