database

The Data Directory Contains an Old postmaster.pid File

PostgreSQL Connection Failure Sometimes, computers have a life of their own. You shut down your laptop and on the next boot, you are unable to connect to a PostgreSQL 12 database. Opening the installed Postgres.app, I notice it displays stale postmaster.pid file error. Postmaster.pid What the heck is the postmaster.pid file? Turns out that the postmaster.pid is a lock file used to prevent two instances of the same PostgreSQL server from running on the same data-directory.

How To Alter a Column Used By A View or Rule

In PostgreSQL, assume you have a table and view with the following definitions: CREATE TABLE boq_items ( id character varying(22) NOT NULL, item_no character varying(50) NOT NULL, activity_name character varying(255) NOT NULL, page_no int NOT NULL, qty numeric(14,2) NOT NULL, rate numeric(14,2) NOT NULL, bq_amt numeric(14,2) NOT NULL ); CREATE VIEW vw_boq_item_names AS SELECT activity_name FROM boq_items; Attempting to change the definition of activity_name column using ALTER TABLE boq_items ALTER activity_name TYPE text, ALTER activity_name SET NOT NULL; will return a cannot alter type of a column used by a view or rule.

How to Drop All Tables in PostgreSQL Database

Introduction The DROP TABLE command in PostgreSQL removes a table definition, all data and indexes of a table from a database. DROP TABLE will fail if the table has other objects that depend on it like views and foreign key definitions. The command will also fail and display a table does not exist message if the table being dropped does not exist. PostgreSQL does not have a drop all tables command and you have to define your own way of performing this task.

Cannot attach database SQL Server 2012 error 5

When upgrading from MS SQL Server 2008 to MS SQL Server 2012, I decided to retain the data directories I was using in SQL 2008. I therefore did a backup and detached all my private databases, removed SQL 2008 and did a clean install of MS SQL 2012. I logged into the new server and on trying to attach back any of my databases, I encountered the following error: CREATE FILE encountered operating system error 5 (Access is denied) while attempting to open or create the physical file .