[GENERAL] How can I run a PostgreSQL database outside /var/run/postgresql?
How can I run a PostgreSQL database independently of the normal packaged based configuration? I just want to start up postgres or pg_ctl process using a different port, pointing to a different data directory and get it running, with permissions etc working okay. I don't want it to depend on the existing package page structure in /etc/postgresql and /var/run/postgresql. The only thing required is database compatiblity with the existing package based installations, which I may be able to install if they are absent The main points are - ** ** 1. Which data and configurations should be copied, ie the files and directories should be copied from /etc/postgresql/xxx and /var/run/postgresql 2. What executables are required, whether they can be extracted from existing packages ie rpm/deb directly or after they have been installations to their locations. 3. My preference will be to use existing package based installations or install them if possible in case of dependencies which are not present in my files, with my executables as there second option. Thanks voipfc
[GENERAL] Is there a way to recover deleted records if database has not been vacuumed?
I am using Django to develop an app and I think I must have done a syncdb (which deletes all records) without realizing it. I have not vacuumed that database and I have also made a copy of the data directory. Is there some way to recover the deleted records? -- Frank Church === http://devblog.brahmancreations.com
Re: [GENERAL] How to erase transaction logs on PostgreSQL
On 8 March 2012 16:23, Guillaume Lelarge guilla...@lelarge.info wrote: On Thu, 2012-03-08 at 10:18 +, Frank Church wrote: How do you purge the postgresql transaction log? You don't. PostgreSQL does it for you. I am creating a virtual machine image and I want to erase any transaction logs that got built up during development. What is the way to do that? I am currently using 8.3 and 8.4. Is there the possibility that the logs saved in /var/log also contain security details? The only security detail AFAIK would be passwords (if you set a password and log queries, the password will be in the logs... the only way to prevent that is to send it crypted). What are the commands to accomplish that, ie getting PostgreSQL to erase the logs? -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
[GENERAL] How to erase transaction logs on PostgreSQL
How do you purge the postgresql transaction log? I am creating a virtual machine image and I want to erase any transaction logs that got built up during development. What is the way to do that? I am currently using 8.3 and 8.4. Is there the possibility that the logs saved in /var/log also contain security details? /voipfc
[GENERAL] What effect does destroying database related threads have on system resources?
An application I have written connects to a remote Postgresql 8.3/8.4 database using Lazarus/FreePascal components, Zeoslib, but it has been timing out too often as the network gets busy in a bursty mode. When the network times out, restarting the app retrieves the data quickly, it is always available in good time. The problem with some of the busier networks is that timeout dialogs happen too often and it annoys the users who have to restart the app. My approach then is to run the database for displaying locally (which is actually the original design) and fetch the data from the remote database in background thread or in a background helper utility and insert it into the local database, where the timeouts do not occur. This creates 2 problems 1. When the connection times out 2. When the query itself times out. Since handling connection timeouts and query timeouts can be fraught,what I want to do is to create the whole process of retrieving the data in a thread within the main program or in a helper utility, and destroying the thread if it times out, but I can't be sure of what happens to the resources created by the thread when it is destroyed, both in the program code itself, the libpq libraries and on the server. What are the consequences of working this way with lipql and the server? Any ideas on what is likely to happen? /voipfcGuy
[GENERAL] How many databases can PostgreSQL open at a time?
I want to migrate a lot of single user databases onto a single server to allow the users to access them remotely, instead of on the workstations. The databases are quite small and even the most heavily used ones only have at most a few hundred records added to them everyday. The problem is they have a high polling rate, about every 5 secs. If I migrate them to a central servers how much hardware will be needed for them to cope? My initial approach was to create views for each user and use updateable views and/or triggers so that the applications can work transparently simply by changing the names of the tables to the names of the views, but I am wondering whether it will be much simpler to use individual databases instead. /voipfc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is it possible to change password though pg_auth?
Is it possible to change password through pg_auth? I am developing a VM where may be necessary to reset passwords through a web interface, without having postgresql actually running. If the users are already set in the database, can the passwords be changed by adding their md5 encoding to pg_auth? Is there a fixed salt for the md5 encoding? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can database run a script automatically at start up?
Can PostgresSQL be setup to automtatically run a script at start up? I need something to run at start up in case the database crashed or some programs did not close their databases properly on shutdown. /voipfc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a way to backup Postgres via SQL commands?
Are there SQL commands that can do a backup over a client connection, rather than from the command line like pgsql etc? By that I mean some kind of SELECT commands that can retrieve the database's content as SQL commands that can be replayed to a server to restore it, rather than something that saves directly to file, or passes it through a pipe? /voipfc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What are the minimal files required to backup a postgresql database
I need to package the ability to backup a PostgreSQL database in my application? I obviously need to add pg_dump.exe to the package, and the dlls required are listed below, 15/08/2007 15:4112,288 comerr32.dll 15/08/2007 15:41 139,264 gssapi32.dll 15/08/2007 15:4119,968 k5sprt32.dll 15/08/2007 15:41 602,112 krb5_32.dll 27/02/2007 15:39 1,040,384 libeay32.dll 31/01/2003 19:41 916,849 libiconv-2.dll 16/03/2004 16:37 898,048 libiconv2.dll 27/04/2004 10:4532,256 libintl-2.dll 06/05/2005 17:52 103,424 libintl3.dll 31/10/2008 07:15 167,936 libpq.dll 05/11/2005 14:51 169,092 libpq81.dll 16/10/2007 13:40 348,160 msvcr71.dll 02/12/2006 00:03 626,688 msvcr80.dll 09/03/2010 16:09 520,190 sqlite3.dll 27/02/2007 15:40 196,608 ssleay32.dll Are there any other executables besides pg_dump I need to add to this list? If some of them are likely to come in useful, rather than necessary for backup I don't mind adding them Regards /voipfc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Minimal Postgresql installation for embedded app on Windows 32
I want to install postgresql as an embedded database and I am looking to install the most minimal installation, with perhaps some support for ODBC. I prefer to install it all in a separate directory so it does not affect any standard PostgreSQL installation on the same computer, running it as a different user, different ports etc. In going through the contents of http://downloads.enterprisedb.com/postgresql/postgresql-8.4.4-1-windows-binaries.zip I see that ODBC is not included, does that require a Stackbuilder installation? If I need to use ODBC, does it have to be a standard installation in the default directories, or can I create a separate configuration still using the standard files, but manage to get the libraries pointed to those of my installation, using a different port? It looks like unzipping and using pgctl can do most of what is required except for the Stackbuilder requirements for ODBC. Can I simply use the StackBuilder installation to install everything into the same subdirectory, zip everything up and extract it do the same folder during installation, then run some initialization scripts to get stuff running as in the standard install? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a builtin function for formatting time values?
I have to this to extract formated times from timestamps, but I suspect there is something much better, even if it uses a stored procedure. The lpad command to is also funny because I have to concatenate it with an empty quote to convert the output to a string. select lpad(extract(hour from calldate) || '', 2, '0') || ':' || lpad(extract(minute from calldate) || '', 2, '0') || ':' || lpad(extract(second from calldate) || '', 2, '0') from asterisk_cdr There must be a formattted into to string function or a formatted time function somewhere. Are there any links to PostgreSQL stored procedure libraries? /voipfc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Stored procedure for generation next sequence value
Is there a built in function for retrieving the next value of a sequence and updating the sequence counter? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] rpm containing pgdump
Which of the postgresql rpms contains pgdump. I have downloaded postgresql-server and postgresql-libs and pgdump is not included. Which rpm contains it? /frank ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] uConnect Voip
Does this device allow connection to other phones besides Skype, like Xten Xlite? http://www.voipvoice.com/UConnect-2.html. Compatibility with standard voip is not mentioned on their website? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] uConnect Voip
Sorry, I realized that. On 9/7/06, Jan de Visser [EMAIL PROTECTED] wrote: On Thursday 07 September 2006 11:38, Frank Church wrote: Does this device allow connection to other phones besides Skype, like Xten Xlite? http://www.voipvoice.com/UConnect-2.html. Compatibility with standard voip is not mentioned on their website? wrong list. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Syntax for converting double to a timestamp
On 9/5/06, codeWarrior [EMAIL PROTECTED] wrote: It's generally considered bad form to use reserved words as column names I am aware of that - in this case the column names are chosen to reflect exactly the names of the attributes of the event being recorded. Frank Church [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I am trying to create a view based on this query 'select *, timestamp::timestamp from ccmanager_log' This is the error I get to below, how do I use the time zone syntax error: cannot cast tupe double precision to timestamp without time zone What is the right syntax? The column to be converted is also called timestamp F Church ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Syntax for converting double to a timestamp
On 9/4/06, Alban Hertroys [EMAIL PROTECTED] wrote: Michael Glaesemann wrote: Note that epoch does not mean 1900-01-01 00:00:00. select *, timestamp 'EPOCH' + timestamp * interval '1 second' as tstamp from ccmanager_log where id 15400 select *, timestamp '1900-01-01 00:00:00' + timestamp * (interval '1 second') from ccmanager_log where id 15400 I tried both of these and the worked - but them timestamp '1900-01-01 00:00:00' gives dates that are 70 years of so it should be '1970-01-01 00:00:00' The sheer guruhood of PostgreSQL users is amazing. Which topics in the manual discusses these issues in depth? Indeed! Where did this 1900 sneak in? Aren't timestamps usually based on epoch? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Syntax for converting double to a timestamp
I am trying to create a view based on this query 'select *, timestamp::timestamp from ccmanager_log' This is the error I get to below, how do I use the time zone syntax error: cannot cast tupe double precision to timestamp without time zone What is the right syntax? The column to be converted is also called timestamp F Church ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Syntax for converting double to a timestamp
On 9/4/06, Peter Eisentraut [EMAIL PROTECTED] wrote: Frank Church wrote: error: cannot cast tupe double precision to timestamp without time zone What is the right syntax? It's not clear what the meaning of a double precision as a timestamp would be. How about you make that explicit: timestamp * interval '1 second' + timestamp '1900-01-01 00:00:00' I tried the query in this form: select *, timestamp * interval '1 second' + timestamp '1900-01-01 00:00:00' from ccmanager_log This is the error message ERROR: operator does not exist: interval + timestamp without time zone HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. The version I am actually on is 7.4 - Hide quoted text - or whatever you had in mind. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Syntax for converting double to a timestamp
Peter's suggestion did not work. On 9/4/06, Michael Glaesemann [EMAIL PROTECTED] wrote: Please reply to the list so that others may contribute to and learn from the discussion. I am now getting familiar with google mail On Sep 4, 2006, at 8:34 , Frank Church wrote: ERROR: function to_timestamp(double precision) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. The version I am on is 7.4 and that function is probably not there That would it be it. You can follow Peter's suggestion or upgrade. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Looking for good PostgreSQL hosters
I am looking for service providers that offer postgresql hosting. One requirement, cheap but reliable (if there is any such thing). It will be good if they can run different versions simultaneously (on different ports of course) If they offer versions of apache, php, mysql etc simultaneously it will be good. I am getting rather tired of having to manage different versions systems in parallel on my own servers and I am looking for provider who can do it all, cheaply of course. Perhaps I should bite the bullet, get a powerful dedicated server and run some VMs, but I just hate to manage servers myself / Frank ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] SQL ASCII encoding
My databases are created in SQL ASCII by default. Is there some disadvantage to this? As a British user, which is the preferred character set and what advantage do I have to gain by using it?database ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: REPOST: [GENERAL] How to determine the table a query or a views columns come from?
Quoting Tom Lane [EMAIL PROTECTED]: Quoting Tom Lane [EMAIL PROTECTED]: Can this info be obtained by querying the system tables, especially in the case of views? I am using 'scripting' languages and using C will be quite awkward. Frank Church [EMAIL PROTECTED] writes: Is there way to determine the table a query or a view's columns come from? Yeah, there's some support for that in the protocol. libpq exposes it as PQftable() and PQftablecol(). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to determine the table a query or a views columns come from?
Quoting Tom Lane [EMAIL PROTECTED]: Can this info be obtained by querying the system tables, especially in the case of views? I am using 'scripting' languages and using C will be quite awkward. I have got to find if libpq's output is exposed in PHP or Ruby. Frank Church [EMAIL PROTECTED] writes: Is there way to determine the table a query or a view's columns come from? Yeah, there's some support for that in the protocol. libpq exposes it as PQftable() and PQftablecol(). regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How to determine the table a query or a views columns come from?
Is there way to determine the table a query or a view's columns come from? I am looking for something like that in postgresql. If it doesn't exist is there some generalized SQL that can parse the query or view's definition and retrieve the list. something like column name | actual column name | source table | actual source table column name is either the original name or an alias for it, and so is source table. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] What is the syntax for setting a default date in PostgreSQL?
This issue always stumps me. I need to set the default date for a column in postgres. A value for today, today + x days, now (timestamp), now + x amount of days. I never seem to be able to locate it in the documentation or online. Can anyone post it here once and for all for posterity? I will be much obliged :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL]
I get this error when I try to delete a column in a table in PG 7.4 ERROR: multiple INTERNAL dependencies for table What could the cause be and how do I get rid of it? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] using schema-qualified names in INSERTs
Does PostgreSQL support INSERT syntax of this kind -
insert into table (table.col1, table.col2, table.col3) values('one', 'two',
'three')?
Trying it out generates an error. It works when the 'table' bit is removed from
the column names.
F Church
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Gotcha's in copying data between servers via file copy
I want to copy the data from one server to another. One is running Postgresql 7.4.6 and the other 7.4.7. Are they any gotchas I should lookout for when making the copy? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] I need some info on repairing database corruption
One of my database tables appears to corrupted see this post - Re: [GENERAL] REPOST:Memory Allocation error using pg_dump on 7.4. On checking the archives it appears I have to use a pg_filedump program to fix it, which doesn't appear to be in my distribution. Where can I find it and install it? In this thread http://archives.postgresql.org/pgsql-admin/2005-03/msg00448.php Tom Lane describes a means of locating the rogue records There is also a means of locating the row similar to a binary search. Is there some automated script that can locate the record the faulty records This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] REPOST:Memory Allocation error using pg_dump on 7.4
I repeatedly get this error whenever I try to backup a database The command used is: pg_dump -Fc -O -U username tablename tablename.20060122 pg_dump: ERROR: invalid memory alloc request size 4294967290 pg_dump: SQL command to dump the contents of table cc_ratecard failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967290 pg_dump: The command was: COPY public.tablename(id, ... Is there a bug somewhere in there? Is there something which needs doing in regard to my memory allocation settings? The table involved is one of the larger tables, but at only 400,000 records it shouldn't be a problem. This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Working out diskspace taken by database and tables 7.4
I am trying to work out how much space is taken up by a given database in the file system. I have googled the archives and found the stuff there looks dated. Is there some script or query that can work it all out? My current version is 7.4. Knowing abut 8.x.x will be fine too? This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Memory Allocation error using pg_dump on 7.4
I repeatedly get this error whenever I try to backup a database The command used is: pg_dump -Fc -O -U username tablename tablename.20060122 pg_dump: ERROR: invalid memory alloc request size 4294967290 pg_dump: SQL command to dump the contents of table cc_ratecard failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967290 pg_dump: The command was: COPY public.tablename(id, ... Is there a bug somewhere in there. This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Does pg_dump have result codes to indicate success or failure
I want to use pg_dump in a php program. Does pg_dump have specific error codes? Does use the normal shell result codes to indicate success or failure ? This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Is there a way to list running queries
Is there a way to list running queries in PostgreSQL, especially those that appear to have gone away or don't seem to be running at all? This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] What is the maximum length of an IN(a,b,c....d) list in PostgreSQL
What is the maximum length of an IN(a,b,cd) list in PostgreSQL? I am using 7.4. This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Is there a way to list runaway queries and kill them?
Is there a way to list runaway or long runninng queries and kill them? Frank This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Loading large amounts of data in a SQL command
I am loading lots of data via SQL into a database and wrapping it into transactions to speed it up. This fails a number of times and causes the whole transaction to fail. The queries results are logged so it is easy for me to find problem records. Is there a setting or feature that allows which allows the same performance as transactions, without causing the whole process to fail, like a delayed updates or write mechanism of some sort? Frank This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How to change database owner in 7.4
Hi guys, What is the command change database owner in PostgreSQL 7.4? I think the 'ALTER DATABASE foo OWNER TO blob;' is not implemented in 7.4 R Church This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How to check options PostgreSQL was started with
How do you check the options a PosgreSQL service was started with? Frank This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
