Basic Error Of PostgreSQL

Error1:
--------

-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres  

psql.bin: could not connect to server: No such file or directory  

Is the server running locally and accepting  

connections on Unix domain socket "/tmp/.s.PGSQL.5435"?  

Cause/Resolution:
--------------------
1. First thing you would need to check is server status(using below commands), if server is not running, start it and try to connect.

-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data/ status  

pg_ctl: server is running (PID: 49230)  

/opt/PostgreSQL/9.3/bin/postgres "-D" "../data"  

  

-bash-4.1$ ps -ef|grep data  

postgres 49230     1  0 13:32 pts/1    00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D ../data  

postgres 57430 27596  0 18:23 pts/1    00:00:00 grep data  

2. If you found that cluster is running and still not able to connect, then check the port number in postgresql.conf file and try to connect using correct port.

-bash-4.1$ grep -i 'port' /opt/PostgreSQL/9.3/data/postgresql.conf  

port = 5435  # (change requires restart)  

  

-bash-4.1$ ls -ltr /tmp/.s.PGSQL.5435*  

-rw-------. 1 postgres postgres 51 Jan 12 13:05 /tmp/.s.PGSQL.5435.lock  

srwxrwxrwx. 1 postgres postgres  0 Jan 12 13:05 /tmp/.s.PGSQL.5435  

  

-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres  

Timing is on.  

psql.bin (9.3.2)  

Type "help" for help.  

postgres=#  

Error2:
--------

-bash-4.1$ ./psql -p 5435 -U postgres -h 192.168.225.185 postgres  

psql: could not connect to server: Connection refused  

Is the server running on host "192.168.225.185" and accepting  

TCP/IP connections on port 5435?  

Cause/Resolution:
--------------------
1. You would need to look at your "listen_addresses" parameter in postgresql.conf file, check if you are set this to allow the other servers to connect.

-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres  

Timing is on.  

psql.bin (9.3.2)  

Type "help" for help.  

postgres=# show listen_addresses ;  

 listen_addresses  

------------------  

localhost  

(1 row)  

2. If you found that you set it to allow, then you will have to look at your firewall setting. May be port is not opened for that server due to security issues.
Error3:
--------

-bash-4.1$ ./psql -p 5435 -U postgres -h 192.168.225.185 postgres  

psql: FATAL:  no pg_hba.conf entry for host "192.168.225.130", user "postgres", database "postgres", SSL off  

Cause/Resolution:
--------------------
As error says, there is no entry for that host in pg_hba.conf file(which is loacted at data directory location). You can add an entry for that host like below:

-- Open pg_hba.conf  

  -bash-4.1$ vi /opt/PostgreSQL/9.3/data/pg_hba.conf  

  -- Add a line like below:  

  host    all             all             192.168.225.130/32            trust  

Note: trust is a type of authentication. you will get more info here: http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html  

  

-- Reload the cluster.  

  

-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data/ reload  

server signaled  

  

-- Now try to connect.  

  

-bash-4.1$ ./psql -p 5435 -U postgres -h 192.168.225.185 postgres  

psql (9.3.0.1, server 9.3.2)  

Type "help" for help.  

postgres=#  

Error4:
--------

-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres -d postgres  

psql.bin: FATAL:  sorry, too many clients already  

Cause/Resolution:
---------------------
This error shows that you are done with max_connections, check the parameter and connections to the cluster from an already connected session:

postgres=# show max_connections ;  

 max_connections  

-----------------  

 2  

(1 row)  

postgres=# select count(*) from pg_stat_activity;  

 count  

-------  

     2  

(1 row)  

To get rid of this, you probably need to increase the parameter or disconnect some "idle" sessions. You will get idle sessions by using below query:

postgres=# select pid,query,state from pg_stat_activity where state like 'idle';  

  pid  | query | state  

-------+-------+-------  

 11855 |       | idle  

(1 row)  

postgres=#  

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where state='idle' and pid <> pg_backend_pid();  

 pg_terminate_backend  

----------------------  

 t  

(1 row)  

postgres=# select pid,query,state from pg_stat_activity where state like 'idle';  

 pid | query | state  

-----+-------+-------  

(0 rows)  

postgres=#  

  

Note: changing any of the above parameters needs a restart of cluster.  

Error5:
--------

-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U test postgres  

psql.bin: FATAL:  remaining connection slots are reserved for non-replication superuser connections  

Cause/Resolution:
--------------------
As error says, remanining connections are reserverd for superusers. So you would need to increase max_connections parameter or decrease the superuser_reserved_connections parameter to connect as *normal* user.
Note: changing any of the above parameters needs a restart of cluster.

Error6:
--------

postgres=# select * from test;  

ERROR:  relation "test" does not exist  

LINE 1: select * from test;  

                      ^  

Cause/Resolution:
---------------------
1. First question, might be silly, but worth to ask, really this table exist?
2. yes, exists, OK, then check whether the table name given is correct or not. You might have created the table with mixed chars(upper/lower). You can get the exact name by using below query:

postgres=# select quote_literal(relname) from pg_class where upper(relname)='TEST';  

 quote_literal  

---------------  

 'TesT'  

(1 row)  

postgres=# select * from "TesT";  

 t  

---  

(0 rows)  

3. Check if you have the table in different schema so that you can specify the schema name explicitly before the table name OR set the schema name in searth_path parameter:

postgres=# \d '*'."TesT"  

      Table "test.TesT"  

 Column |  Type   | Modifiers  

--------+---------+-----------  

 t      | integer |  

  

  

-- So you have table in "test" schema, then use below query or set search_path like below:  

  

postgres=# select * from "test"."TesT";  

 t  

---  

(0 rows)  

postgres=# set search_path to "test";  

SET  

postgres=# select * from "TesT";  

 t  

---  

(0 rows)  

Error7:
--------

testdb=# drop user bob;  

ERROR: role "bob" cannot be dropped because some objects depend on it  

DETAIL: owner of table bobstable  

owner of sequence bobstable_id_seq  

Cause/Resolution:
---------------------
you must either be a superuser or have the CREATEROLE privilege.

The easiest solution to this problem is to not drop the user at all, but just disallow the user from connecting:

pguser=# alter user bob nologin;

For dropping such user, there are two methods:

1. Reassign all the objects owned by the user to some other user and then drop the user.
Above is very useful, if employee, who left the company, has written some Procedure/objects, which is getting used in Application/process.

Command Which can be are following
 REASSIGN OWNED BY old_role to new_role;  

    DROP USER old_role;   

Note:: reassign command need to be executed for all the databases under one PG instance.  

2. First Drop all the objects owned by the user and then drop the user.
This is useful if admin don't want to keep the users objects and wants to drop all the objects owned by user:
Command which can be use are following:
DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ];

    DROP user username;   

Note:: DROP OWNED BY NAME need to be executed for all the database.  

Error8:
--------

"LOG: out of file descriptors: Too many open files in system; release and retry"  

Cause/Resolution:
--------------------
If you see this error Message in Log file then consider reducing Potgres's max_files_per_process setting. Postgres itself will usually not have a serious problem when you've run the kernel out of file descriptors or use ulimit -n command max_files_per_process to something less than whatever per-process file limit the kernel is enforcing.
Error9:
--------

postgres=> copy test from '/tmp/test.txt';  

ERROR:  must be superuser to COPY to or from a file  

HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.  

Cause/Resolution:
---------------------
As error says, a normal user can't copy from a file to a table. You can use "\COPY" instead.

postgres=> select current_user;  

 current_user  

--------------  

 test  

(1 row)  

postgres=> \copy test from '/tmp/test.txt';  

postgres=> select * from test;  

 t  

---  

 1  

 2  

 3  

 4  

 5  

(5 rows)  

OR
To let user "test" copy directly from file, the superuser can write a special wrapper function for "test" user, as follows:  

create or replace function copy_for_testuser(tablename text, filepath text)  

   returns void  

   security definer  

   as  

   $$  

    declare  

    begin  

         execute 'copy ' || tablename || ' from ''' || filepath || '''';  

    end;  

   $$ language plpgsql;  

  

postgres=# \c postgres test  

You are now connected to database "postgres" as user "test".  

postgres=>  

postgres=> select copy_for_testuser('test','/tmp/test.txt');  

 copy_for_testuser  

-------------------  

  

(1 row)  

postgres=> select * from test;  

 t  

---  

 1  

 2  

 3  

 4  

 5  

(5 rows)  

Error10:
---------
ERROR:  canceling statement due to statement timeout
Cause/Resolution:
--------------------
Use statement timeout to clean up queries which take too long. Often you know that you don't have any use for queries running more than x times. Maybe your web frontend just refuses to wait for more than 10 seconds for a query to complete and returns some default answer to users if it takes longer, abandoning the query.
In such a case, it is a good idea to set statement_timeout = 15 sec either in postgresql.conf or as a per user or per database setting, so that queries running too long don't consume precious resources and make others' queries fail as well.
The queries terminated by statement timeout show up in log as follows:
 test=# set statement_timeout = '3 s';

   SET  

   test=# select wait(10);  

   ERROR:  canceling statement due to statement timeout  

They used to show up as a more confusing "query canceled due to user request" on the older version of PostgreSQL.

Interview Q and A for Oracle12c Part - 1

1. What are the major changes in architecture for 12c

From 12c Onwards, the instance is shared with multiple databases.

This multiple databases are self-contained and pluggable from one database to another database. This is very useful methodology where database consolidation.

In short a single sga and background process will be shared to multiple databases, the databases can be created on fly and drop or attach and detach from one server to another server.

2. What are the common concepts of multitenant database?

Multitenant database consists of

1. CDB is a container database which is similar like standalone database. Called CDB$ROOT
2. PDB$SEED is a template database to create a databases within the CDB databases
3. PDB<n> are individual or application databases
4. Data dictionary between this databases are shared via internal links called object link and data link
5. Users between CDB and PDB are different, there will be common users (starts with C##) and local users
6. When the CDB starts up, the PDB will be in mount state, you must open them exclusively

3. Methods to create Multitenant Database

1. Via DBCA when creating database there is an check box "enable container database" and then provide PDB details, this will automatically creates the CDB,PDB$SEED, PDB databases
2. Via create database command and keep a clause enable container database

4. How to convert a normal database to Container enabled database?

1) Upgrade and set comptabile = 12.0.0
2) Open the database in readonly mode
3) Execute the dbms_pdb package describe procedure
BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => ‘/disk1/oracle/ncdb.xml’); END; /
4) Shutdown the non-cdb database
5) If in same server your CDB database contains
CREATE PLUGGABLE DATABASE ncdb USING ‘/disk1/oracle/ncdb.xml’ COPY FILE_NAME_CONVERT = (‘/disk1/oracle/dbs/’, ‘/disk2/oracle/ncdb/’);
6) Execute the script
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
7) Open the read write mode new PDB

5. How to create a new PDB database?

1. Copying

SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password 
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M) DEFAULT TABLESPACE sales        DATAFILE ‘/disk1/oracle/dbs/salespdb/sales01.dbf’ SIZE 250M AUTOEXTEND ON 
PATH_PREFIX = ‘/disk1/oracle/dbs/salespdb/’ 
FILE_NAME_CONVERT = (‘/disk1/oracle/dbs/pdbseed/’, ‘/disk1/oracle/dbs/salespdb/’);

2. Cloning
1. CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
3. Plugging in
1. Create an xml file
2. create pluggable database pdb_plug_nocopy using ‘/u01/app/oracle/oradata/pdb1.xml’
   NOCOPY
   TEMPFILE REUSE;
3. alter pluggable database pdb_plug_nocopy open;

6. How to distinguish you are in CDB or PDB?

Once you logged in you can check show con_name or con_id will show you which db you are in

7. How to connect to PDB from a CDB?

In a container enabled databases , once the pdb is created , a service will be automatically created on same name for example pdb

1. Either using TNS Entries SQLPLUS sys/****@pdb as sysdba

2. Log into cdb and then alter session set container=pdb

3. EZconnect method sqlplus

8. How about the datafiles system, sysaux , undo, redo etc , does they create when you create PDB?

1. Datafiles are individual to each database for cdb and each pdb
2. Undofiles and redofiles are only one across container
3. Tempfiles can be created in each database or share one across all databases
4. SGA is shared across all databases
5. Background process are shared across all databases , no additional back ground process defined

9. As you said, if SGA and background process are shared, is there any performance impact,

Ideally this architecture is used for database consolidation projects which and where small databases are shared in a single database host and not that high critical applications running. This leverages the reduction in licensing cost and also resource utilization effectively.

10. How does the data dictionary works to manage multiple database, for example if I used PDB1 to select a query from emp.

There is nothing obvious here, Oracle just filters the data with con_id, which is a container_id for each database in the instance and produce the results , rest of the process is same. To understand this check any v$ view or dba_view you will find the con_id column which states that what database that row belongs to.  This is something like VPD where the rows will be masked and present to the database users where they have privileges, similarly the rows will be shown only what database you have logged in.