[ADMIN] graceful reload

[Vasiliy G Tolstov]

Hello.

I'm use linux and postgresql under xen vps. Vps supports memory balloon
(i need to increase memory some time). Can postgresql server has ability
to graceful reload itself after it configuration files changed?
Thank You.

-- 
Vasiliy G Tolstov 
Selfip.Ru


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Restore DataBase

[Eric McKeeth]

On Tue, Jun 1, 2010 at 9:26 PM, ALEXANDER JOSE  wrote:

>   psql coon I'm trying to restore a database that has 60 GB in size, where
> there is a table with 38 million records, has raised 18 million records when
> the restore process fails on a syntax error, the backup was made with a
> file. sql as the best way I can do to restore??
>
>
It will be easier for people to help if you provide more info such as
postgres version, OS, how the backup was made, exact error message, etc.
But, if you're getting a syntax error, then my best guess would be that
either that you have a corrupt/incomplete backup, or that you're trying to
restore to a different version of postgres than you used to make the backup,
and the backup has syntax which would be valid for the version that made it,
but not for the version you're trying to restore to.

-Eric

Re: [ADMIN] pitrtools package wrong dependency (binary packages)

[Devrim GÜNDÜZ]

On Wed, 2010-06-02 at 16:03 -0300, DANIEL CRISTIAN CRUZ wrote:
> I was trying to setup a test environment with PostgreSQL 8.2 +
> Heartbeat + 
> pitrtools, but the package in 
> http://yum.pgsqlrpms.org/8.2/redhat/rhel-5Server-x86_64/pitrtools-1.2-3.rhel5.noarch.rpm
>  
> seems to have a wrong dependency (postgresql-server >= 8.3).
> 
> Is it correct? 

Actually that package either

1) should not exist in 8.2 repository. It depends on pg_standby
internally, and pg_standby is not available in RPMs.

2) should not depend on -contrib package for 8.2, and let the user
install pg_standby manually.

I think the good way is (2).

Regards,
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

[ADMIN] Strange performance issue apparently causd by pg_stat timeout

[Benjamin Krajmalnik]

System is running PG 8.4.0 (I have been unable to upgrade because the
system needs to be up 24x7), FreeBSD 7.2 amd64, 8 cores, 16GB RAM.

Our application is a network monitoring system, so we are constantly
inserting vast amounts of data (server presently processes about 50
million transactions per day).

As digests of test points come in, they are stored in message queues on
a second server (running PG 8.4.4).  A set of daemons process the
digests and insert the data into the main database residing on a second
server.  Presently, the database has about 60GB of data.

 

A few days ago, I noticed that the data in the message queues on the
other server was getting backed up, and then after a few minutes it
would process and clear.  This was a totally new behavior.   Initially I
suspected deadlocks caused by background processes which create
materialized views, so I stopped those, yet the behavior continued.
Then I suspected server load, yet CPU utilization and load was fine (l
minute and 5 minute load was at < 4), and iostat did not show an overly
busy disk subsystem (I had seen it at much higher utilization levels on
both the data and log partitions without any performance hits).

 

I then suspected network issues, so I checked the infrastructure (runnig
on Juniper Gig switches, of course non-blocking, and all of the port
information was clean).

Finally, I noticed that the logs had "pg_stat wait timeout" warnings.
Initially I though these were caused by our checking the running process
via pgadmin from our office to the data center, yet even when I exited
pgadmin, the warnings were still there.

After further testing, I saw a correlation between the data getting
queued up and the "pg_stat wait timeout" warnings.  As data would begin
to queue up, I would see the warnings, and about a minute later it would
start to dequeue and get stored on the server.

 

I searched the archives and found some messages stating that this has
been observed.  The interesting thing is that nothing has changed on the
server and it started to manifest itself.  I ran an analyze of the
entire database, hoping this may rectify any issue, but unfortunately to
no avail.

 

Any suggestions would be deeply appreciated - this behavior is
definitely not good.

 

Below is a snapshot from the log files:

 

2010-06-02 20:59:19 MDT WARNING:  pgstat wait timeout

2010-06-02 21:10:42 MDT WARNING:  pgstat wait timeout

2010-06-02 21:16:21 MDT WARNING:  pgstat wait timeout

2010-06-02 21:21:23 MDT WARNING:  pgstat wait timeout

2010-06-02 21:25:50 MDT WARNING:  pgstat wait timeout

2010-06-02 21:35:20 MDT WARNING:  pgstat wait timeout

2010-06-02 21:39:13 MDT WARNING:  pgstat wait timeout

2010-06-02 21:45:32 MDT WARNING:  pgstat wait timeout

 

Thanks in advance.

Re: [ADMIN] pitrtools package wrong dependency (binary packages)

[Lacey Powers]

DANIEL CRISTIAN CRUZ wrote:

Hi all,

I was trying to setup a test environment with PostgreSQL 8.2 + 
Heartbeat + pitrtools, but the package in 
http://yum.pgsqlrpms.org/8.2/redhat/rhel-5Server-x86_64/pitrtools-1.2-3.rhel5.noarch.rpm 
seems to have a wrong dependency (postgresql-server >= 8.3).


Is it correct? Does someone know how to ask for help about this?

Thanks,

Daniel Cristian Cruz



Hello Daniel,

Having a look, that is most definitely not correct. =(

I'll make the changes in our spec file, and create a revised set of 
packages as soon as possible.


In the future, you can email us at:

pgc...@lists.commandprompt.com*

*or you can go here to report an issue:

https://public.commandprompt.com/projects/pitrtools

Sorry for the troubles. =(

Regards,

Lacey

--
Lacey Powers

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 104
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Exception while accessing database

[Kevin Grittner]

Manohar Bhattarai  wrote:
 
> FATAL: password authentication failed for user "postgres"
 
> But the password is the same that i use to login postgres user in
> the terminal.
> What could be the problem?
 
When you say "postgres user in the terminal", do you mean logging in
to the operating system?  If so, that has nothing to do with the
database login unless you choose to make it so by having an "ident"
entry in the pg_hba.conf file.
 
http://www.postgresql.org/docs/8.3/interactive/client-authentication.html
 
> I am not able to understand how to start creating a new user
> and a new database after a fresh installation of postgres.
 
Once you get logged in with the postgres user, that would be the
CREATE DATABASE and CREATE USER statements:
 
http://www.postgresql.org/docs/8.3/interactive/sql-createdatabase.html
 
http://www.postgresql.org/docs/8.3/interactive/sql-createuser.html
 
-Kevin

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] FATAL: pg_hba.conf ::1

[Scott Marlowe]

On Tue, Jun 1, 2010 at 7:55 AM, Fredy Ganga (GMAIL ACCOUNT)
 wrote:
> Hello:
>
> I have been testing streaming replication on postgresql 9, and now i have
> this messages in pg_log on salve.
> pg_log:
> 2010-06-01 09:13:58 CLT LOG:  el sistema de bases de datos fue interrumpido;
> última vez en funcionamiento en 2010-06-01 09:00:17 CLT
> 2010-06-01 09:13:58 CLT LOG:  entering standby mode
> 2010-06-01 09:13:58 CLT LOG:  redo comienza en 0/1020
> 2010-06-01 09:13:58 CLT LOG:  registro de longitud cero en 0/10A8
> 2010-06-01 09:13:58 CLT LOG:  streaming replication successfully connected
> to primary
> 2010-06-01 09:13:59 CLT FATAL:  el sistema de base de datos está iniciándose
> 2010-06-01 09:14:00 CLT FATAL:  el sistema de base de datos está iniciándose
> 2010-06-01 09:14:00 CLT LOG:  consistent recovery state reached at
> 0/1100
> 2010-06-01 09:14:00 CLT LOG:  database system is ready to accept read only
> connections
> 2010-06-01 09:14:01 CLT FATAL:  no hay una línea en pg_hba.conf para «::1»,
> usuario «postgres», base de datos «postgres», SSL inactivo
> 2010-06-01 09:14:02 CLT FATAL:  no hay una línea en pg_hba.conf para «::1»,
> usuario «postgres», base de datos «postgres», SSL inactivo
>
> IPV6
> My pg_hba on slave:
> # TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
> # IPv4 local connections:
> host    all             all             127.0.0.1/32            md5
> #host replication postgres 192.168.XXX.XX/16 trust
> host all all 192.168.XXX.XX/16 trust
> # IPv6 local connections:
> #host    all             all             ::1/128                 md5
> ENVIRONMENT:
> DB: Postgresql 9
> Master: Windows XP
> Slave: Windows 2008 Server
> Maybe this problem is'nt important to the replication process.. but... i
> would like to resolve it...
> Thanks for your help.

Looks like it's trying to use IPv6 and there's no entries for IPv6 in
your pg_hba.conf to math.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Installing PostgreSQL

[Scott Marlowe]

On Tue, Jun 1, 2010 at 10:01 AM, Daniel, Clyde D (Derrick)
 wrote:
>
> Good Afternoon,
>
>
>
>     I am trying to install PostgreSQL on my desktop and it is 
> asking for a password.  Everything I type in is not working .

Try a really complex password like:

azxygrh123DF$%yhn

etc.  numbers, upper and lower case and non-alphanumeric characters
and see if that works.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] Modificar tamaño de pg_xlog

[mario martinez hernandez]

Buenas tardes.
Mi nombre es Mariano.
Mi pregunta es.
¿ Es posible modificar el tamaño de pg_xlog? ¿como? ¿donde?
Gracias
Un saludo.


  

[ADMIN] Installing PostgreSQL

[Daniel, Clyde D (Derrick)]

Good Afternoon,

 

I am trying to install PostgreSQL on my desktop and it is 
asking for a password.  Everything I type in is not working it gives me this
error:

 

 

 

Any help would be greatly appreciated.

 

Thanks

 

<>

[ADMIN] FATAL: pg_hba.conf ::1

[Fredy Ganga (GMAIL ACCOUNT)]

Hello:


I have been testing streaming replication on postgresql 9, and now i have
this messages in pg_log on salve.

pg_log:
2010-06-01 09:13:58 CLT LOG:  el sistema de bases de datos fue interrumpido;
última vez en funcionamiento en 2010-06-01 09:00:17 CLT
2010-06-01 09:13:58 CLT LOG:  entering standby mode
2010-06-01 09:13:58 CLT LOG:  redo comienza en 0/1020
2010-06-01 09:13:58 CLT LOG:  registro de longitud cero en 0/10A8
2010-06-01 09:13:58 CLT LOG:  streaming replication successfully connected
to primary
2010-06-01 09:13:59 CLT FATAL:  el sistema de base de datos está iniciándose
2010-06-01 09:14:00 CLT FATAL:  el sistema de base de datos está iniciándose
2010-06-01 09:14:00 CLT LOG:  consistent recovery state reached at
0/1100
2010-06-01 09:14:00 CLT LOG:  database system is ready to accept read only
connections
*2010-06-01 09:14:01 CLT FATAL:  no hay una línea en pg_hba.conf para «::1»,
usuario «postgres», base de datos «postgres», SSL inactivo*
*2010-06-01 09:14:02 CLT FATAL:  no hay una línea en pg_hba.conf para «::1»,
usuario «postgres», base de datos «postgres», SSL inactivo*


IPV6

My pg_hba on slave:
# TYPE  DATABASEUSERCIDR-ADDRESSMETHOD

# IPv4 local connections:
hostall all 127.0.0.1/32md5
#host replication postgres 192.168.XXX.XX/16 trust
host all all 192.168.XXX.XX/16 trust
# IPv6 local connections:
#hostall all ::1/128 md5

ENVIRONMENT:
DB: Postgresql 9
Master: Windows XP
Slave: Windows 2008 Server

Maybe this problem is'nt important to the replication process.. but... i
would like to resolve it...

Thanks for your help.


Fredy

Re: [ADMIN] Firebird To postgresql migration help needed

[Lew]

Scott Marlowe wrote:

I use nano or vi for most stuff.  Both can do color coding quite nicely
for .sql files.


mnavahan wrote:

i [sic] need powerful IDE for it with modernize options (extended editor) rich
good 


Scott Marlowe wrote:

Got me.  Maybe eclipse has a nice plugin?


I find both Eclipse and NetBeans to be quite sufficient as SQL editors and 
admin/edit/query front ends for any database system that has a JDBC driver. 
They probably aren't quite as useful if you aren't using Java, although both 
have C/C++ capability.  I'm not familiar with other IDEs.


No extra plugins were required with the current versions that I downloaded, 
namely NetBeans 6.8 or 6.9beta or Eclipse Galileo SR2.


The IDEs have additional advantages besides managing databases and editing SQL 
and program source code, such as providing quite convenient management 
consoles for popular Java application servers such as Tomcat or various Java 
EE products, and handling applications' build and deployment phases.


YMMV.  They might represent overkill for some folks.  I enjoy the convenience 
of having everything under one roof.


--
Lew

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] pitrtools package wrong dependency (binary packages)

[DANIEL CRISTIAN CRUZ]

Hi all,

I was trying to setup a test environment with PostgreSQL 8.2 + Heartbeat + 
pitrtools, but the package in 
http://yum.pgsqlrpms.org/8.2/redhat/rhel-5Server-x86_64/pitrtools-1.2-3.rhel5.noarch.rpm 
seems to have a wrong dependency (postgresql-server >= 8.3).


Is it correct? Does someone know how to ask for help about this?

Thanks,

Daniel Cristian Cruz 



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] Exception while accessing database

[Manohar Bhattarai]

Hi,
This problem was initially asked to pgsql-j...@postgresql.org and I was
suggested to ask this problem here.
So my initial problem was that i was not able to set the Driver for
postgresql. I solved that problem with the help there.

But I have a new exception and it is :
org.postgresql.util.PSQLException: FATAL: password authentication failed for
user "postgres"

I am giving the line for connection that i am using :
connection = DriverManager.getConnection(connectionURL, "postgres",
"mypasswordhere");

But the password is the same that i use to login postgres user in the
terminal.
What could be the problem?


I am using (Linux)Ubuntu 8.04 as OS, Postgresql is 8.3, java is jdk1.6,
using Eclipse as IDE, and trying to make a project in jsp.

My problem is that I had installed postgresql8.3 successfully. and after
some search on the google gave me some configuration details which i
followed. But I am not able to understand how to start creating a new user
and a new database after a fresh installation of postgres.

 Can you provide me with come details to how to create a new user and new
database after a new installation of postgres?

If you need any more details i will provide them. Please help me solve this
problem.
-- 
Regards,
Manohar Bhattarai (मनोहर भट्टराई)
Blogs:
http://manoharbhattarai.wordpress.com/
http://manoharbhattarai.posterous.com/
http://manoharbhattarai.blogspot.com/
Microblogs:
Twitter :- http://twitter.com/manoharmailme
Identi.ca :- http://identi.ca/manoharbhattarai

Re: [ADMIN] Restore DataBase

[Andreas Schmitz]

hi,

sounds like a plain text backup. your problem, I guess, will be some 
constraint issue. I suggest commenting out the constraints in the backup 
file before running the restore.


regards

andreas


ALEXANDER JOSE wrote:
psql coon I'm trying to restore a database that has 60 GB in size, 
where there is a table with 38 million records, has raised 18 million 
records when the restore process fails on a syntax error, the backup 
was made with a file. sql as the best way I can do to restore??



Alexander Angel
Venezuela


Invite your mail contacts to join your friends list with Windows Live 
Spaces. It's easy! Try it! 




--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin