SQL-Postgres Database Community Blog: Postgresql interview questions

Click on each Question to check the Answers

you are not able to connect to postgres server in remote machine and recieving an error psql:could not connect to server: connection refuse.

1.set the listener ip address in your postgresql.conf to "*" instead local ip
2.set the pgport and pghost environment variables at the client machine

When you are creating a database . it raises an error "create db : command not found".

find the bin directory in the installation path and execute the psql from that path. 2)check whether postgres installed on this machine and running.

All internal commands in postgres apart from psql starts with

how to see all the command help in postgres

list of all postgres internal commands

one of the table is using the system key word (user) as one of the column name. create table table statement on this table is failing.

include user in quotes. eg:, create table customers ( customerid int,"user" varchar(30)

how to upload a text file to a table

use the copy command. copy customers from 'c:\customers.txt';

Explain all types of joins. inner,left outer , right outer, full

http://www.calsql.com/2008/11/exploring-sql-server-joins.html

What is Write Ahead Log

write-ahead log (WAL), means it always writes the transactions to the log following with writing the modified pages to the disk to maintain the transaction ACID properties.

How to take a copy of a table

Create table My_Table_1 as select * from My_table

What are the options available for DRP

SAN Replication
Block Device Replication (only works on Linux and other 3rd party utilities)
Log Shipping – File Based or Stream Based (synchronous & asynchronous)
Replication – Master-standy (asynchronous)
Multi-master replication – merges the data between servers(make sure about conflicts arised)

What are the basic steps to partition a table

1.create master table ( * don’t create any constraints here)
2.create child tables ( with inherits master_table , WITH a check constraint to make sure that the data will split into multiple tables based on the check condition)

how to export all the objects in a database with create table scripts with data(full backup with data included in insert statements)

pg_dump databasename > outfile
** to restore psql dbname <outfile

what is the maximum limitation of indexes on a table

unlimited

what are the steps to create a clustered index on a table

create table employees (iid int)

create index employees_ind on employees (iid)

CLUSTER employees USING employees_ind;