Re: [GENERAL] How can I tell if pg_restore is running?
My extension has a config table that is dumped by pg_dump and populated by pg_restore. However, this table has triggers on it that I would like not to do anything if the table is being populated by pg_restore. I want the triggers to operate only if the user is manipulating the table directly after the database has been restored and is running. Is there a way for my extension's trigger functions to return immediately when triggered by pg_restore? If this is a data only restore, and you are running as a superuser, you can not simply specify pg_restore --disable-triggers ? = Romax Technology Limited A limited company registered in England and Wales. Registered office: Romax Technology Centre University of Nottingham Innovation Park Triumph Road Nottingham NG7 2TU United Kingdom Registration Number: 2345696 VAT Number: 526 246 746 Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] Dump Database
pgsql-general-ow...@postgresql.org wrote on 17/03/2014 12:50:20: From: José Pedro Santos zpsant...@hotmail.com To: Postgres Ajuda pgsql-general@postgresql.org, Date: 17/03/2014 12:56 Subject: [GENERAL] Dump Database Sent by: pgsql-general-ow...@postgresql.org Dear all, I'm trying to dump my database using the following command after I enter as su postgres: pg_dump - U postgres mydb -f mydb.sql ... but I'm always having the following message: pg_dump [archiver] Could not open output file mydb.sql : Permission denied I try to use the same statement using su.. but I didn't manage. Can someone give me a help? I'm using Ubuntu 12.04 LTS and Postgres 9.1. Kind Regards, José Santos The assumption here is that you're in a directory where you do not have permissions. try: # cd # pg_dump -f mydb.sql -U postgres mydb It also appears that you have a space between - and U, make sure that it appears: -U. Cheers = Romax Technology Limited A limited company registered in England and Wales. Registered office: Rutherford House Nottingham Science and Technology Park Nottingham NG7 2PZ England Registration Number: 2345696 VAT Number: 526 246 746 Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
[GENERAL] C Function Memory Management
Ok, so just a (hopefully) quick question. I know that I should be using the Postgres function palloc in place of malloc, but what about realloc? Is there an equivalent? Thanks. = Romax Technology Limited Rutherford House Nottingham Science Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] C Function Memory Management
Hi Pavel yes, repalloc Maybe I should've looked a little harder for that one! Ha Ha! I assume it's an exact functional copy of realloc, and see where I get with it! Many Thanks. = Romax Technology Limited Rutherford House Nottingham Science Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] C Function Memory Management
One thing to know about it is that repalloc re-sizes the given chunk while keeping it in the same memory context it was originally allocated in (ie, CurrentMemoryContext doesn't matter). This may not matter much if you're only dealing with allocations that just live as long as the current function call --- but if you're trying to preserve data across calls, it's critical to know. regards, tom lane Thanks for this Tom, I'll keep that in mind for future functions. In this particular implementation, it's not necessary to preserve the data once the function is complete. So out of curiosity, what would be your approach for data preservation cross call? Cheers Martin = Romax Technology Limited Rutherford House Nottingham Science Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] C Function Memory Management
Usually I'd allocate the space in fcinfo-flinfo-fn_mcxt (which is a query-lifespan context) and keep a pointer to it in fcinfo-flinfo-fn_extra (which is a void * field that a C function can do what it pleases with). There are plenty of examples in the Postgres source code. If you are looking to cache data longer than one query then it takes more thought. regards, tom lane Thanks Tom. I'll have a look into this when I have more time. Kind Regards. Martin. = Romax Technology Limited Rutherford House Nottingham Science Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] Problem with aborting entire transactions on error
if { no error during query } {
do it
} else {
withdraw this one
if { ROLLBACK_ON_ERROR } {
rollback entire transaction
}
}
I fear that this is no-where near as simple as it's been taken for, and
without looking at the code, I would imagine that this would touch so many
different areas, that a simple implementation is just not possible.
Ask yourself; How do other DBMS systems handle this?
MySQL has INSERT IGNORE
MSSQL has PRIMARY KEY WITH IGNORE_DUP_KEY
Oracle has a couple of ways of doing this, one of which logs the data
(using a DBMS package) the other is with MERGE.
So, maybe you need to have a look at a postgres sepcific way of doing
this, seeing as other DBMS's can't seem to agree? Maybe shorter save
points so you lose only partial data?
Also, you might want to read some of the discussions here, as in my
opinion, what you're looking for is best solved with MERGE.
http://wiki.postgresql.org/wiki/SQL_MERGE
Cheers
=
Romax Technology Limited
Rutherford House
Nottingham Science Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=
===
E-mail: i...@romaxtech.com
Website: www.romaxtech.com
=
Confidentiality Statement
This transmission is for the addressee only and contains information that
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf
of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your
system and contact the sender. Thank you for your cooperation.
=
Re: [GENERAL] remote connection refused
Usually, after what you've already confirmed, it's likely to be one of the following: - check listen_address is set correctly in postgresql.conf. (try listen_address = * and restart postgres) - check port 5432 is open on iptables. (service iptables status, netstat -a | grep 5432) Cheers From: Yvon Thoraval yvon.thora...@gmail.com To: pgsql-general@postgresql.org pgsql-general@postgresql.org, Date: 19/11/2012 12:14 Subject:[GENERAL] remote connection refused Sent by:pgsql-general-ow...@postgresql.org I'd like to have a checklist upon what to do, what to investigate, when an external connection is refused. if i connect by : psql -h localhost -p 5432 -U yt mydb it works as usual however even locally but using the LAN address : psql -h 192.168.0.20 -p 5432 -U yt mydb i got a : psql: could not connect to server: Connexion refusée Is the server running on host 192.168.0.20 and accepting TCP/IP connections on port 5432? obviously I've verified by ping the computer is reachable. something i suspect : $ lal /var/run/postgresql total 12 drwxrwsr-x 2 postgres postgres 140 nov. 19 09:53 . drwxr-xr-x 26 root root 900 nov. 19 09:45 .. -rw--- 1 postgres postgres 6 nov. 19 09:53 9.1-main.pid -rw-r--r-- 1 postgres postgres 4 nov. 19 07:10 pgbouncer.pid srwxrwxrwx 1 postgres postgres 0 nov. 19 09:53 .s.PGSQL.5432 -rw--- 1 postgres postgres 71 nov. 19 09:53 .s.PGSQL.5432.lock srwxrwxrwx 1 postgres postgres 0 nov. 19 07:10 .s.PGSQL.6432 this the locked file : .s.PGSQL.5432.lock is that normal or not ? something to say, i had a brutal shutdown due to over heating of my laptop recently. on the pg_hba.conf i do have a bunch of IPV6 addresses allowing connection then i don't think the prob comes from here, but nevermind... in such a situation what is your checklist in order to find what I've missed ? -- Yvon = Romax Technology Limited Rutherford House Nottingham Science Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] Need psql send email
Hi All,I am new to postgresql. I want to send email by using pl
pgsql. I want
to know how to set up the configurations for mail server.Can any one
help me
in solving this?. pavithra@gmail.com
http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/
Best regards,
depesz
Alternatively:
CREATE OR REPLACE FUNCTION sendmail(p_from text, p_to text, p_subject
text, p_content text)
RETURNS void AS
$BODY$
use strict;
use warnings;
my ($from, $to, $subject, $content) = @_;
open(MAIL, |/usr/sbin/sendmail -t) or die 'Cannot send mail';
print MAIL From: $from\n;
print MAIL To: $to\n;
print MAIL Subject: $subject\n\n;
print MAIL $content;
close(MAIL);
$BODY$
LANGUAGE plperlu;
Works ok provided sendmail is configured.
or:
CREATE OR REPLACE FUNCTION send_smtp(p_mail_host text,
p_from text,
p_to text,
p_subject text,
p_content text,
p_timeout integer DEFAULT 60,
p_debug integer DEFAULT 0,
p_exactaddr integer DEFAULT 1,
p_skipbad integer DEFAULT 1)
RETURNS void AS
$BODY$
use strict;
use warnings;
use Net::SMTP;
no strict 'refs';
my ($host, $sender, $recipient, $subject, $body, $timeout, $debug, $exact,
$skipbad) = @_;
(!defined($host) || !($host)) die 'No SMTP host provided.';
(!defined($sender) || !($sender)) die 'No sender address/name
provided.';
(!defined($recipient) || !($recipient)) die 'No recipient address
specified.';
my $mail = Net::SMTP-new(
Host = $host,
Debug = $debug,
Timeout = $timeout,
ExactAddresses = $exact
) or die 'Net::SMTP-new() Failed';
$mail-mail($sender);
$mail-recipient($recipient, { SkipBad = $skipbad });
$mail-data();
$mail-datasend(MIME-Version: 1.0\n);
$mail-datasend(From: . $sender . \n);
$mail-datasend(To: . $recipient . \n);
$mail-datasend(Reply-To: . $sender . \n);
$mail-datasend(Subject: . $subject . \n\n);
$mail-dataend();
$mail-quit();
$BODY$
LANGUAGE plperlu;
Feel free to hack away as much as required.
Both of these work fine provided PL/PerlU is installed and the server is
properly configured on the network, and that there is a valid SMTP mail
host to receive.
Cheers
Martin
=
Romax Technology Limited
Rutherford House
Nottingham Science Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=
===
E-mail: i...@romaxtech.com
Website: www.romaxtech.com
=
Confidentiality Statement
This transmission is for the addressee only and contains information that
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf
of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your
system and contact the sender. Thank you for your cooperation.
=
Re: [GENERAL] Need psql send email
The 1st one seems OK in a scary-from-a-security-standpoint kind of way. Agree, it needs to be weighed up and assessed from a security stand point I guess. The 2nd, not so much. See http://stackoverflow.com/questions/12002662/psql-trigger-send-email Imagine if the DNS goes wonky. Do you want all your backends tied up in DNS lookups? Or timing-out TCP connections? Agree 100%, which is why I noted: the server is properly configured on the network... I suppose you could always provide an IP address as the mail host. This function is only a Quick Knock Together job, that works readily enough. IMHO There's always an inherent risk with any form of sending mail from an RDBMS, whether it be abuse or otherwise, however; it's one of those situations where needs must, and more often than not must be done quickly. I would guess that having SMTP built into the DB engine itself would be no less susceptible to abuse or problems than any other method (For example UTL_SMTP in Oracle, which I've had hang before due to issues with SMTP servers). I guess it's one of those where you just have to weigh up the options and choose the best one for your situation/application. Cheers Martin = Romax Technology Limited Rutherford House Nottingham Science Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] At what point does a big table start becoming too big?
I have a table with 40 million rows and haven't had any performance issues yet. Are there any rules of thumb as to when a table starts getting too big? For example, maybe if the index size is 6x the amount of ram, if the table is 10% of total disk space, etc? -- My rule here is that a table is too big when performance starts degrading beyond an acceptable level. If the database and server are delivering consistent and acceptable performance levels despite an index being 6x RAM or a table consuming 10% of disk, then I tend to leave it be until an issue is raised.CheersMartin= Romax Technology Limited Rutherford House Nottingham Science & Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] At what point does a big table start becoming too big?
I have a table with 40 million rows and haven't had any performance issues yet. Are there any rules of thumb as to when a table starts getting too big? For example, maybe if the index size is 6x the amount of ram, if the table is 10% of total disk space, etc?My rule here is that a table is too big when performance starts degrading beyond an acceptable level. The challenge there is that if you wait until performance degrades beyond an acceptable level, you've allowed yourself to get into a situation where clients are upset and frustrated, and fixing the problem is difficult because there's so much data to manipulate to rearrange things. Apologies, I could/should have phrased that better..My rule here is that a table is too big when performance starts degrading beyond a MEASURABLE level. :)CheersMartin = Romax Technology Limited Rutherford House Nottingham Science & Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] How to stop a query
As Scott mentioned, kill -9 on a Postgres process is not a wise idea on a Postgres process.If you query is coming from another application, then terminating that application with a kill -9 *may* work, but is, as scott says, a last resortI tend to use kill -TERM (15) to disconnect the client, which gives the log message terminating connection due to administrator commandkill -INT (2) gives the cancelling statement due to user request and does not disconnect the client.So it depends on what you want to do.If i have a runaway query (not so common on 9.1 now), Then i'll try the above, and if they don't work, then i'll try an /etc/init.d/postgresql stop or a pg_ctl stop -m f. then restart the server.Only if that does not work will i consider killing using a -9.Cheerspgsql-general-ow...@postgresql.org wrote on 19/07/2012 17:25:57: From: younus younus.essa...@gmail.com To: pgsql-general@postgresql.org, Date: 19/07/2012 20:30 Subject: Re: [GENERAL] How to stop a query Sent by: pgsql-general-ow...@postgresql.org Hi, Yes, I'm sure, it's work. if you execute query by another program (program java), you must use the first solution [ps -ef | grep postgres and kill -9 (PID of your query)]. if you use pgsql terminal and you're connecting with postgres you can use select procpid, datname, usename, client_addr, current_query from pg_stat_activity where current_query!='IDLE'; SELECT pg_cancel_backend (procpid); Younus. -- View this message in context: http://postgresql. 1045698.n5.nabble.com/How-to-stop-a-query-tp1924086p5717297.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general = Romax Technology Limited Rutherford House Nottingham Science & Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
