Messages posted by: Venkat_B

Thanks Vibhor,

It helped me in learning one new thing today.

I have changed the parameter "unit_socket_directory" in my postgresql.conf file to point the postgresql sokcet lock files to other location(/home/postgres/tmp) during startup. But the other two commands which i have mentioned is also working fine to get connected with new "unit_socket_directory" location.

I was not clear how it's working, anyway thanks again for giving new source/command to get connected when the parameter is changed.

Now these three commands are working to get connected:

cmd3: /opt/PostgreSQL/8.4/bin/psql -d template1 -U postgres -h localhost -p 5432
cmd4: /opt/PostgreSQL/8.4/bin/psql -d template1 -U postgres -h 127.0.0.1 -p 5432
cmd5: psql -h <socket_directory_path>(Ex: psql -h /tmp/socket_test/)

I have tested it with my test box too and it's great....

Thanks,
Venkat.

Hi,

I'm sorry i have tested only cmd1 and cmd 2 from which i mentioned in above message:

cmd1: psql template1
cmd2: /opt/PostgreSQL/8.4/bin/psql -d template1 -U postgres

It's working fine with these commands:

cmd3: /opt/PostgreSQL/8.4/bin/psql -d template1 -U postgres -h localhost -p 5432
cmd4: /opt/PostgreSQL/8.4/bin/psql -d template1 -U postgres -h 127.0.0.1 -p 5432

Sorry again and thanks for support.

Thanks,
Venkat.

Hi,

Normally as i'm connecting from localhost itself i just give "psql template1". But in this case i have tested with both cases where the result is same error.

These are the following commands i used to connect:

cmd1: psql template1
cmd2: /opt/PostgreSQL/8.4/bin/psql -d template1 -U postgres
cmd3: /opt/PostgreSQL/8.4/bin/psql -d template1 -U postgres -h localhost -p 5432
cmd4: /opt/PostgreSQL/8.4/bin/psql -d template1 -U postgres -h 127.0.0.1 -p 5432

Where the result was same error.

Thanks,
Venkat

Hi,

I installed PostgreSQL 8.4 Version in my test machine. Installation was successful and everything goes fine(like creating DB, connection and creation of tables etc.,). Now i tried to change the parameter unix_socket_directory to point the postgres startup socket lock file to some other location other than /tmp. When i start the PostgreSQl it's starting the DB and socket lock file got created at mentioned path, but when trying to connect from command prompt to any DB it's throwing error like:

psql: 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.5432"?

I also tried by mentioning the path in .bash_profile but there is no luck.

Do i need to mention or define some in any other files.

Thanks in advance for support.

Thanks,
Venkat.

we enable for 5 autovacuum process. There are few running from past 8hrs and 2 processes running from past 2 days stiil not completed even after changing the parameters.

--Venkat

Oh......

There is no luck even after changing the autovacuum and vacuum_cost_delay to zero.

--Venkat

Scottie,

we set vacuum_cost_delay and autovacuum_cost_delay to 1000.I'll check by making it to zero. Thanks for that.

Also the dataset we have for those tables are like 4 to 6 millions.

ISTM that you may need to be vacuuming them more regularly to avoid this issue.

Can you please explain ISTM means and how it works in resolving the issue.

Thanks in advance.

--Venkat

Hi,

We are using PostgreSQL 8.3.7 version and our DB size is around 200GB and its OLTP database. We are having partition tables and maintains for 120 days data.

But from couple of days we are getting the processes slow down and i noticed the following processes like:
procpid | usename | query_start | run_time | waiting | current_query
---------+----------+---------------------+-----------------+---------+---------------------------------------------------------------------------------------------------------------------
14971 | postgres | 2009-11-07 16:17:49 | 5 days 13:15:51 | f | autovacuum: VACUUM test.tr_td_dimension_2009_08_21 (to prevent wraparound)
19674 | postgres | 2009-11-11 09:43:15 | 1 day 19:50:25 | f | autovacuum: VACUUM test.tr_stg_hr_dimension_2009_08_18 (to prevent wraparound)
28064 | postgres | 2009-11-10 12:26:26 | 2 days 17:07:14 | f | autovacuum: VACUUM test.tr_td_dimension_2009_08_18 (to prevent wraparound)
17044 | postgres | 2009-11-12 17:03:06 | 12:30:34 | f | autovacuum: VACUUM ANALYZE test.dn_rule_data (to prevent wraparound)
25602 | postgres | 2009-11-11 22:12:49 | 1 day 07:20:51 | f | autovacuum: VACUUM test.tr_td_dimension_2009_08_02 (to prevent wraparound)

We enabled autovacuum for every 2million records.... but these processes are runnning for days and which is causing for the partition tables creation and delinking failures and also the drastic performance failure.

Is there any way where we can reduce the time of running of these autovacuum processes.

--Venkat

Oh...Sorry and Thanks Scottie.

--Venkat

Hi,

I would like to install PostgreSQL 8.3.8-1 using postgresql-8.3.8-1-linux-x64.bin file. But i'm having problem when i try to execute the binary file:

./postgresql-8.3.8-1-linux-x64.bin
-bash: ./postgresql-8.3.8-1-linux-x64.bin: cannot execute binary file

Also i gave chmod +x permissions to the file.

We are using the OS:
uname -a
Linux localhost.localdomain 2.6.9-78.ELsmp #1 SMP Wed Jul 9 15:39:47 EDT 2008 i686 i686 i386 GNU/Linux

Can anyone help me to get continued.I searched for the solution but not found docs on this.

--Venkat

Thanks for that.

I think pg_fsck is not supported for Pg Plus 8.3 version. We check for the better possibilities which can minimize the administrator risks/tasks.

--Venkat

Have you configured HA solution in your environment ?

Can you please explain or give me some knowledge about this HA solution.... What exactly is this as i have no idea about HA solution.

what my doubt is now we are getting error while querying between some range. What is the solution if i have the same with just select count(*) from test_table;

How can i resolve if we found the particular range/records which is causing problem by checking with different ranges.

Is the only solution to load table from backup / direct reloading or do we have any other possibilities where we can resolve the issue like moving the bad blocks data to other blocks....etc.,

---Venkat

Hi Scottiebo and Sam,

Thanks for your reply. I am running just count(*) when i got this error:
(select count(*) from test_table where columnid between 10000 and 20000;)

Is dropping the entire table and reloading is the only solution or do we have any other?

Because if i have million of rows with table and i have this error ... then even reloading data will be hard.

If it is like problem with hardware(disk in read-only mode) then it not suppose to retrieve data from other tables also.But we are able to retrieve data from other tables which are stored in same disk.

Thanks,
Venkat

Hi,

What would be the reason for such errors:

NOTICE: -20000could not read block 11079 of relation 1228638/406241/1743636: read only 0 of 8192 bytes

Is this because of any corrupted data inserted or bad block with hardware.

Is there any way to resolve this kind of error. This is the problem getting while trying to retrieve data.

Thanks,
Venkat.

Hi,

I have the following problem with postgres while starting:

LOG: loaded library "$libdir/plugins/plugin_debugger"
LOG: could not create IPv6 socket: Address family not supported by protocol
LOG: database system was interrupted while in recovery at 2009-09-02 14:13:49 IST
HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery.
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 1154/1601E0A0
FATAL: could not create relation 582578/582570/2798264: File exists
CONTEXT: xlog redo insert: rel 582578/582570/2798264; tid 418/24
LOG: startup process (PID 6939) exited with exit code 1
LOG: aborting startup due to startup process failure

We don't have any backup's.......is there any other way to clear the problem and up the postgres database.

We are using Postgres 8.3 version.

Thanks,
Venkat

Hi,

I'm using Postgres 8.3.3 and Grid 1.0 . I'm having one problem :

While loading data in to Grid table which has no primary or secondary keys on table is showing following error:

2009-08-13 22:14:40,305 - ERROR Catching throwable:
java.io.IOException: testdbN15@myserver: loader failed to load data: com.edb.util.PSQLException: SERROR^@C23505^@Mduplicate key value violates unique constraint "idx_xrowid_sample_test_240709"^@WCOPY sample_test_240709, line 7: "20090724174922_104543,NA,,112775075209,321818020471498,,,,,,,,,,,,20090724,987174138,20090724,11220153..."^@Fnbtinsert.c^@L298^@R_bt_check_unique^@^@
at com.edb.gridsql.engine.loader.EDBWriter.stopEDBCopyThread(Unknown Source)
at com.edb.gridsql.engine.loader.EDBWriter.finish(Unknown Source)
at com.edb.gridsql.engine.loader.DataProcessorThread.finishLoad(Unknown Source)
at com.edb.gridsql.engine.loader.Loader.finishLoad(Unknown Source)
at com.edb.gridsql.parser.SqlCopyData.execute(Unknown Source)
at com.edb.gridsql.engine.ExecutableRequest.execute(Unknown Source)
at com.edb.gridsql.engine.XDBSessionContext.executeRequest(Unknown Source)
at com.edb.gridsql.engine.ServerStatement.execute(Unknown Source)
at com.edb.gridsql.engine.ServerStatement.execute(Unknown Source)
at com.edb.gridsql.engine.XDBSessionContext.executeRequest(Unknown Source)
at com.edb.gridsql.protocol.PgProtocolSession.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)

If any one have idea why this problem persists please guide me where to check to clear this problem.

Thanks,
Venkat

Hi,

I'm having the following error:

org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)
at mondrian.rolap.SqlStatement.execute(SqlStatement.java:137)
at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:244)
at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:205)
at mondrian.rolap.SqlMemberSource.getMemberChildren2(SqlMemberSource.java:719)
at mondrian.rolap.SqlMemberSource.getMemberChildren(SqlMemberSource.java:649)
at mondrian.rolap.SqlMemberSource.getMemberChildren(SqlMemberSource.java:624)
at mondrian.rolap.RolapCubeHierarchy$RolapCubeHierarchyMemberReader.readMemberChildren(RolapCubeHierarchy.java :456)

Using PostgresPlus 8.3 and GridSQL 1.0 version

Thanks,
Venkat

Hi Mason,

We are using the very recent taken from CVS.

And here is one case where we got the same error:

GridSQL -> create table csdtn_test (col1 varchar(100),col2 varchar(20),col3 date,col4 NUMERIC(17,2),col5 integer,col6 VARCHAR(5)) partitioning key col1 on all tablespace tbs_grid;
OK
Response time: 0m 0s 164ms Total time: 0m 0s 164ms

GridSQL -> copy csdtn_test from '/home/postgress/test.csv' delimiter ',' CSV;
OK
Response time: 0m 0s 429ms Total time: 0m 0s 429ms

GridSQL -> select count(*) from csdtn_test;
+----------+
| count(*) |
+----------+
| 18000 |
+----------+
1 row(s).
Response time: 0m 0s 133ms Total time: 0m 0s 133ms

GridSQL -> create table test_csdtn partitioning key col1 on all tablespace tbs_chennai_grid as select col1,case when ltrim(rtrim(col6))='4' then 'ABC' else 'DEF' end ST,sum(case when col4=0 then 0 else col5 end) from csdtn_test where col2='NW' group by col1,case when ltrim(rtrim(col6))='4' then 'ABC' else 'DEF' end;
SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT .....................

And in console log the error is:

eQS Node 6 has aborted execution, cause is: com.edb.gridsql.exception.XDBServerException : Can not send data to Nodes
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(Unknown Source)
at com.edb.gridsql.queryproc.QueryProcessor.executeQueryExecPlan(Unknown Source)
at com.edb.gridsql.queryproc.QueryProcessor.execute(Unknown Source)
at com.edb.gridsql.parser.SqlCreateTable.execute(Unknown Source)
at com.edb.gridsql.engine.ExecutableRequest.execute(Unknown Source)
at com.edb.gridsql.engine.XDBSessionContext.executeRequest(Unknown Source)
at com.edb.gridsql.engine.ServerStatement.execute(Unknown Source)
at com.edb.gridsql.engine.ServerStatement.describe(Unknown Source)
at com.edb.gridsql.engine.XDBSessionContext.describeStatement(Unknown Source)
at com.edb.gridsql.protocol.PgProtocolSession.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
Caused by: com.edb.gridsql.exception.XDBMessageMonitorException: Node 6 has aborted execution, cause is: com.edb.gridsql.exception.XDBServerException : Can not send data to Nodes
at com.edb.gridsql.engine.MessageMonitor.checkMessages(Unknown Source)
at com.edb.gridsql.engine.MultinodeExecutor.executeStep(Unknown Source)
... 13 more
Caused by: com.edb.gridsql.exception.XDBWrappedException: com.edb.gridsql.exception.XDBServerException : Can not send data to Nodes
at com.edb.gridsql.engine.NodeProducerThread.processStep(Unknown Source)
at com.edb.gridsql.engine.NodeProducerThread.run(Unknown Source)
... 1 more

2009-04-09 18:57:38,763 - ERROR Throwing throwable:
com.edb.gridsql.exception.XDBServerException: Can not send data to Nodes
at com.edb.gridsql.engine.ProducerSender.sendToNodes(Unknown Source)
at com.edb.gridsql.engine.NodeProducerThread.processStep(Unknown Source)
at com.edb.gridsql.engine.NodeProducerThread.run(Unknown Source)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.io.IOException: testdbN3@127.0.0.1: loader failed to load data: com.edb.util.PSQLException: SERRORCXX000Mcould not open relation with OID 11004649Fheapam.cL869Rrelation_open
at com.edb.gridsql.engine.loader.EDBWriter.stopEDBCopyThread(Unknown Source)
at com.edb.gridsql.engine.loader.EDBWriter.finish(Unknown Source)
at com.edb.gridsql.engine.loader.DataProcessorThread.finishLoad(Unknown Source)
at com.edb.gridsql.engine.loader.Loader.finishLoad(Unknown Source)
... 4 more

Thanks,
Venkat.

Hi Mason,

Yes we are using the latest ones.
(PostgresPlus 8.3.3 GridSQL 1.0)

Thanks,
Venkat

Hi Mason,

I'm not getting the error everytime.

Most of the time i see the error with Create Table AS statement. As i have 20 to 30k records in my test tables, while fetching the data from those tables also i'm getting the same error. I'm trying to trace the problem but it's like once in a while. But still i'll just come with clear scenario where i'm facing this problem for maximum number of times.

Thanks,
Venkat.

Hi Mason,

I'm still getting the same problem. I'm able to connect to the node database from all other locations and servers.

May be permissions is one issue but coming to my scenario i think that's not problem with permissions.

And also there is no problem from SQL end. CREATE table AS is one situation where i'm getting this problem.

Thanks,
Venkat.

Hi Mason,

Thanks for reply ......

Here we are using that query to check for multiple days.The pasted is which i tested with sample data.
The query which you placed is working fine, but i just want to know why the above statement is throwing error and what does that error means exactly?

Thanks if you provide some detailed explanation for the same error.

Thanks,
Venkat.

Hi,

We are using PostgresPlus 8.3.3 and GridSQL 1.0. I'm having the following error. Can anyone suggest to overcome the problem.

GridSQL -> create table sample_test(col1 integer,col2 date,col3 varchar(10)) partitioning key col1 on nodes 1,3,5,7 with XROWID tablespace tbs_test;
OK
Response time: 0m 0s 454ms Total time: 0m 0s 454ms

and loaded 8million data..... and

GridSQL -> select to_char(col2,'DD-MM-YYYY') from sample_test limit 3;
+------------+
| to_char |
+------------+
| 05-11-2008 |
| 05-11-2008 |
| 05-11-2008 |
+------------+
3 row(s).
Response time: 0m 1s 12ms Total time: 0m 1s 13ms

GridSQL -> select count(1) from sample_test where col2 between to_date('05-11-2008','DD-MM-YYYY') and to_date('05-11-2008','DD-MM-YYYY');
SQLException: ERROR: Can not prepare request: null

The problem is the same above command is running for small set of data (tested with 1000 records)

But coming to huge data we are having the problem.

where as this statement is running fine

GridSQL -> select count(1) from sample_test where to_char(col2,'DD-MM-YYYY') between '05-11-2008' and '05-11-2008';
+---------+
| count |
+---------+
| 8487656 |
+---------+
1 row(s).
Response time: 1m 17s 948ms Total time: 1m 17s 948ms

Any quick reply can help me to move forward.

Thanks,
Venkat

Hi,

We are using PostgresPlus 8.3 version and GridSQL 1.0 .
And our server configuration is Rack server with five individual servers having 16GB ram each and are dual quad core machines.
We made maximum changes to the configuration file but yet we are having some issues like db is getting hanged after few transactions, automatically the db is getting down and going to recovery mode etc..

And our DB size is of 400G per month.

Sorry for placing very long file .... can any one suggest is there any other parameters needed to change for smooth usage of DB.

Here is the present configuration file which we are using...

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some paramters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytes MB = megabytes GB = gigabytes
# Time units: ms = milliseconds s = seconds min = minutes h = hours d = days

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)' # write an extra PID file
# (change requires restart)

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 1024 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You might
# also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#password_encryption = on
#db_user_namespace = off

# Kerberos and GSSAPI
#krb_server_keyfile = '' # (change requires restart)
#krb_srvname = 'postgres' # (change requires restart, Kerberos only)
#krb_server_hostname = '' # empty string matches any keytab entry
# (change requires restart, Kerberos only)
#krb_caseins_users = off # (change requires restart)
#krb_realm = '' # (change requires restart)

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 1024MB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 512MB # min 64kB
maintenance_work_mem = 1024MB # min 1MB
#max_stack_depth = 2MB # min 100kB

# - Free Space Map -

max_fsm_pages = 204800 # min max_fsm_relations*16, 6 bytes each
# (change requires restart)
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# (change requires restart)

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
# (change requires restart)
shared_preload_libraries = '$libdir/plugins/plugin_debugger' # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # immediate fsync at commit
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
wal_buffers = 128kB # min 32kB
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds

#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000

# - Checkpoints -

checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 900 # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 is off

# - Archiving -

#archive_mode = off # allows archiving to be done
# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# time; 0 is off

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 40 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 2048MB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0

# - Other Planner Options -

default_statistics_target = 500 # range 1-1000
constraint_exclusion = on
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
# can include strftime() escapes
#log_truncate_on_rotation = off # If on, an existing log file of the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
#log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 to disable.
#log_rotation_size = 10MB # Automatic rotation of logfiles will
# happen after that much log output.
# 0 to disable.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -

#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error

#log_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic

#log_error_verbosity = default # terse, default, or verbose messages

#log_min_error_statement = error # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)

#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this time.

#silent_mode = off # DO NOT USE without syslog or
# logging_collector
# (change requires restart)

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_hostname = off
#log_line_prefix = '' # special values:
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than specified size;
# -1 disables, 0 logs all temp files
#log_timezone = unknown # actually, defaults to TZ environment
# setting

#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

#track_activities = on
#track_counts = on
#update_process_title = on

# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least that time.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#session_replication_role = 'origin'
#statement_timeout = 0 # 0 is disabled
#vacuum_freeze_min_age = 100000000
#xmlbinary = 'base64'
#xmloption = 'content'

# - Locale and Formatting -

datestyle = 'Postgres,European'
#timezone = unknown # actually, defaults to TZ environment
# setting

#timezone_abbreviations = 'India'
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database
# encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'

# - Other Defaults -

#explain_pretty_print = on
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''

#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------

#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.

#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -

#add_missing_from = off
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
escape_string_warning = off
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on
#standard_conforming_strings = off
#synchronize_seqscans = on

# - Other Platforms and Clients -

#transform_null_equals = off

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

#custom_variable_classes = '' # list of custom variable class names

Thanks,
Venkat