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