Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
* From: Brian Crowell On Mon, Nov 11, 2013 at 11:56 PM, Christian Ullrich ch...@chrullrich.net wrote: On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell br...@fluggo.com wrote: * If I don't specify my username, Npgsql sends it in lowercase bcrowell Hmm. That is related one problem I've been having with SSPI auth from libpq/ODBC. The database treats the claimed user name case-sensitively when looking up the user info in pg_authid, and if the user logged on to Windows with a name differing in case from what the database thinks it is, authentication fails. Npgsql sending it always in lower case is precisely what I landed on as a workaround (basically overriding libpq's automatic user name detection in the ODBC connection string by appending a UID option). The message I get in the log is provided user name (bcrow...@realm.com) and authenticated username (bcrow...@realm.com) do not match, so it looks like I have to teach Npgsql to match whatever Windows is sending in GSSAPI. That, or teach Postgres how to lowercase the name on arrival. What did you do to get around this? ODBC supports several connection string types. The simplest is the name of a system or user DSN alone. Another is something along the lines of DSN=xyz;Option1=foo;Option2=bar, supplementing (or overriding) options from the DSN with local values. I used that to supply an explicit UID option giving the result of converting the current user name to another format using IADsNameTranslate. That works because it pulls the information from the directory rather than just munging the result of GetUserName(). Pseudocode: n = GetUserNameEx(NameSamCompatible)// logon screen case NameTranslate.Set(ADS_NAME_TYPE_NT4, n) n = NameTranslate.Get(ADS_NAME_TYPE_DOMAIN_SIMPLE) // official case n = n.CutAtTheAtSign() db.Connect(DSN=foo;UID= + n) To get a usable realm name, ADS_NAME_TYPE_USER_PRINCIPAL_NAME is probably more correct. This works if the role name in pg_authid matches the user name in the directory, case-wise. It cannot be shortened to GetUserNameEx(NameUserPrincipal) because that also returns logon screen case. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
* From: Brian Crowell On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell br...@fluggo.com wrote: I think I'm getting closer though. I have psql on Windows successfully authenticating, so I can't be too far off. Got it. Great! The NpgsqlPasswordPacket class has a bug: a utility function it calls appends a null character to the data, which completely screws up GSSAPI. Now that I fixed that, I've got successful integrated authentication from Windows to PostgreSQL on Linux. However: * If I don't specify my username, Npgsql sends it in lowercase bcrowell Hmm. That is related one problem I've been having with SSPI auth from libpq/ODBC. The database treats the claimed user name case-sensitively when looking up the user info in pg_authid, and if the user logged on to Windows with a name differing in case from what the database thinks it is, authentication fails. Npgsql sending it always in lower case is precisely what I landed on as a workaround (basically overriding libpq's automatic user name detection in the ODBC connection string by appending a UID option). * Use kerberos package in AcquireCredentialsHandle call instead of negotiate As long as it is the client that does that, it should be fine. According to the documentation on SSPI packages, it is valid for the client SSPI to send a GSSAPI token to a server using the Negotiate package (instead of going through SPNEGO to arrive at the same protocol). Also, in my case, it doesn't seem to matter for the SPN whether the service name is postgres or POSTGRES. I've got PostgreSQL set to Yeah, I think that bit about you have to make the service name uppercase in postgresql.conf is some kind of oral tradition that everyone quotes at everyone else. I have been using SSPI and GSSAPI since the days of Windows 2000, and it has always worked quite well without it. postgres, and Npgsql is specifying POSTGRES, but I also at some point configured two sets of SPNs on the domain for uppercase and lowercase, so I don't know if that's a mitigating factor. The client gets its service ticket, with only one service name in it, before contacting the server, so it cannot know what the server wants to see. Congratulations on getting it to work. I'm a bit envious that you beat me to it (GSS auth interop between PostgreSQL on Windows and others is kind of my hobby), but the sooner, the better. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
* Stephen Frost wrote: * Brian Crowell (br...@fluggo.com) wrote: However, the eventual goal was to connect to this same server from a .NET app running on Windows, and here I've run into a snag. The Npgsql library does not support GSSAPI—it only supports SSPI, which is nearly-but-not-enough-like the same thing to count in this situation, Uhhh, why not? Because the server on Linux sends AUTH_REQ_GSS, which Npgsql does not recognize. I tried to fix it using the reverse of they one-line fix that worked in both JDBC and libpq. There, the problem was that they only supported GSSAPI and had no clue about SSPI (except libpq on Windows). The fix was to basically declare GSSAPI and SSPI to be the same. It didn't work. In Npgsql's case, the problem is the other way around -- it only knows SSPI. While making GSSAPI the same as SSPI should work in principle, there must be some difference somewhere. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
* Stephen Frost wrote: * Christian Ullrich (ch...@chrullrich.net) wrote: I tried to fix it using the reverse of they one-line fix that worked in both JDBC and libpq. There, the problem was that they only supported GSSAPI and had no clue about SSPI (except libpq on Windows). The fix was to basically declare GSSAPI and SSPI to be the same. It didn't work. If Npgsql does the same as libpq-on-Windows, it should all work just fine.. Hence my suspicion that it doesn't. I did not have the time to compare every function call yet. In Npgsql's case, the problem is the other way around -- it only knows SSPI. While making GSSAPI the same as SSPI should work in principle, there must be some difference somewhere. Well, what happened after you hacked Npgsql? It's possible there's a Nov 1 10:21:44 infra1 postgres[24864]: [7-1] FATAL: GSSAPI authentication failed for user chris Nov 1 10:25:27 infra1 postgres[25030]: [7-1] FATAL: accepting GSS security context failed Nov 1 10:25:27 infra1 postgres[25030]: [7-2] DETAIL: An unsupported mechanism was requested: Unknown error Nov 1 10:26:28 infra1 postgres[25079]: [7-1] FATAL: accepting GSS security context failed Nov 1 10:26:28 infra1 postgres[25079]: [7-2] DETAIL: An unsupported mechanism was requested: Unknown error Nov 1 10:30:41 infra1 postgres[25193]: [7-1] FATAL: canceling authentication due to timeout Nov 1 10:31:50 infra1 postgres[25277]: [7-1] FATAL: accepting GSS security context failed Nov 1 10:31:50 infra1 postgres[25277]: [7-2] DETAIL: An unsupported mechanism was requested: Unknown error Nov 1 10:39:31 infra1 postgres[25587]: [7-1] FATAL: accepting GSS security context failed Nov 1 10:39:31 infra1 postgres[25587]: [7-2] DETAIL: Unspecified GSS failure. Minor code may provide more information: Nov 1 10:44:32 infra1 postgres[25778]: [7-1] FATAL: accepting GSS security context failed Nov 1 10:44:32 infra1 postgres[25778]: [7-2] DETAIL: Unspecified GSS failure. Minor code may provide more information: Nov 1 10:44:56 infra1 postgres[25789]: [7-1] FATAL: accepting GSS security context failed Nov 1 10:44:56 infra1 postgres[25789]: [7-2] DETAIL: Unspecified GSS failure. Minor code may provide more information: At some point during that I changed the principal that Npgsql gets its service ticket for from POSTGRES/IP address to POSTGRES/host name. There is a comment in the source that it does not work with the host name, with no more details, and I chose not to believe that. The result did nothing to prove me right, though. I think it was where the errors change from accepting context failed to unspecified error, but I may be wrong. The GSSAPI error messages are of the usual helpful kind, even including the colon that is followed by no detail. I will spend more time on it once I have managed to keep my job this week. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
* Francisco Figueiredo Jr. wrote: On Tue, Nov 5, 2013 at 2:35 PM, Christian Ullrich ch...@chrullrich.net mailto:ch...@chrullrich.net wrote: * Stephen Frost wrote: * Brian Crowell (br...@fluggo.com mailto:br...@fluggo.com) wrote: However, the eventual goal was to connect to this same server from a .NET app running on Windows, and here I've run into a snag. The Npgsql library does not support GSSAPI—it only supports SSPI, which is nearly-but-not-enough-like the same thing to count in this situation, Uhhh, why not? Because the server on Linux sends AUTH_REQ_GSS, which Npgsql does not recognize. I tried to fix it using the reverse of they one-line fix that worked in both JDBC and libpq. There, the problem was that they only supported GSSAPI and had no clue about SSPI (except libpq on Windows). The fix was to basically declare GSSAPI and SSPI to be the same. It didn't work. In Npgsql's case, the problem is the other way around -- it only knows SSPI. While making GSSAPI the same as SSPI should work in principle, there must be some difference somewhere. Did you make your changes in the NpgsqlState.cs file? Yes. Also, while checking this part of the code, it seems Npgsql isn't handling the AuthenticationGSS message. It is only handling AuthenticationGSSContinue messages. I think you could try adding the AuthenticationGSS case to Npgsql and see if it can authenticate correctly. You could add a second switch case below the case AuthenticationRequestType.AuthenticationSSPI and see if Npgsql can also handle the GSS authentication correctly. That is exactly what I did. I remember from my work on libpq that there is a slight difference in how it handles the two authentication types, but there it is just a flag for whether to treat the user name case-sensitively or not. Here, I control the case of the user part of the UPN, the claimed user name in the startup packet, and the role name in the database, and I know they are all identical. Therefore it should not matter for now whether Npgsql has similar logic already. To make GSSAPI support production-ready, I may have to add it, of course. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
* Christian Ullrich wrote: Nov 1 10:31:50 infra1 postgres[25277]: [7-1] FATAL: accepting GSS security context failed Nov 1 10:31:50 infra1 postgres[25277]: [7-2] DETAIL: An unsupported mechanism was requested: Unknown error Nov 1 10:39:31 infra1 postgres[25587]: [7-1] FATAL: accepting GSS security context failed Nov 1 10:39:31 infra1 postgres[25587]: [7-2] DETAIL: Unspecified GSS failure. Minor code may provide more information: name, with no more details, and I chose not to believe that. The result did nothing to prove me right, though. I think it was where the errors change from accepting context failed to unspecified error, but I may be wrong. For accepting context failed read unsupported mechanism, of course. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL connect with Visual C++
* dhaval257 wrote: No It is not using .NET framework. I tried with libpq but ended up with error that Application can not run because SSLEAY32.dll is missing (something like this). So I left that thing. Have you used libpq? ssleay32.dll is part of the PostgreSQL distribution, and is installed in the /bin/ directory. Since you are getting that error for ssleay32.dll and not for libpq.dll, I assume you copied libpq.dll somewhere. Copy ssleay32.dll to the same place. You will probably get similar errors for other DLLs; all of them are likely to be in the PostgreSQL /bin/ directory. As an alternative, you can just add that directory to your %PATH% variable. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FATAL : could not read directory base: Invalid argument
* Abraham, Danny wrote: FATAL : could not read directory base: Invalid argument 8.2.4 on Windows. Service will not start. Any idea The data directory path should be quoted, but yours only has the closing quote. The parameter should look like -D S:\ome\path\goes\here , so add the opening quote back. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I cannot insert bengali character in UTF8
* AI Rumman wrote:
I am using database with UTF8 and LC_CTYPE set as default value in
Postgresql 9.1.
But I cannot insert bengali character in a column.
Query Failed:INSERT into tracker (user_id, module_name, item_id,
item_summary) values ('1','Leads','353','বাংলা টেস্��...')::ADODB
error::-ERROR: invalid byte sequence for encoding UTF8: 0xe0a62e
E0 A6 2E is not valid UTF-8: 1110 10100110 00101110
The lead byte indicates that the codepoint consists of three bytes,
but only the very next byte is a trail byte (10..). The third
byte is a single character, a period (.), to be exact.
Setting the MSB on the third byte gives us
1110 10100110 10101110 = E0 A6 AE
, which is a valid UTF-8 encoding of U+09AE BENGALI LETTER MA.
Check your input data.
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql Data directory Issue
* Adarsh Sharma wrote: 2. I installed Postgresql-8.3 in new server and trying to use the data directory but it faces the below error :- Existing data directory is not empty and it cannot able to use it . First, be very, very careful. initdb already saved your data from destruction once, but you should never have allowed it anywhere near your live data in the first place. Also, do not tempt fate: Make sure that your new installation is as close to identical to the old one as possible (architecture, endianness, word size). Starting PostgreSQL on an incompatible data directory should fail reliably, but it sounds as if you do not have current backups. If your data is on some sort of shared storage, maybe you can take a snapshot before you do any more experiments? Install with a different data directory (or make sure it is not mounted while you are installing), then reconfigure before starting the database. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Would it be possible
* Adarsh Sharma wrote: In the evening, by mistake I issued a *drop database globedatabase* command. Is it possible to get the data back till the state before drop database command. My pglog files is in the E:/data directory Binary log is also enabled. You do not mention that you have a file-system level backup from before the DROP DATABASE. Assuming you do not have one, then no, it is not possible. You cannot restore WAL to a cluster that is not in the same state it was in when that WAL was generated. If you have the fs-level backup (the kind of backup you need to use pg_start_backup()/pg_stop_backup() to create), as well as all WAL segments from before the time of that backup until the moment you want to go back to, then you can do it (but only for the entire cluster, so you might need to install Postgres somewhere else just for recovery). Look in the manual under Point In Time Recovery (PITR). -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Would it be possible
* Adarsh Sharma wrote: I have following files in my pg_xlog directory : 000100070091 [...] 000100070098 I think I issued the drop database command 1 month ago. From the manual, I understand that my segment files are recycled to newer ones : PostgreSQL always writes WAL, but to be able to use it for PITR, you have to use WAL archiving. If you don't, the log files are only usable for crash recovery (bringing the tables back to a consistent state after the power fails or Postgres or your OS crashes). I recommend that you read the whole Backup and Restore chapter in the manual, and set up a test environment in which you can do some experiments to make sure you understand how the system works and what you can do in any given situation. /My archive_status folder is empty. How would we know that which data these segment files corresponds too. WAL is a continuous stream of changes to the database, on a fairly low level. Inserting data into a table affects not only the table itself, but also indexes, maybe some statistics. The WAL files contain all these individual updates, mixed together. If you still had the WAL from when you inserted the data, it might be possible to extract the raw data from them (other people have tried), but ... I followed below steps 1 month ago : 1. Load globdatabase through backup.sql (21 GB)file 2. Insert some data near about 3-4 tables ( KB) data. 3. Drop database globdatabase. 4. Load globdatabase through backup.sql (21GB)file May be there is chance because we work very rarely on that system. Now i have the backup file bt I want that 3-4 tables. ... by reloading the database after the DROP without WAL archiving enabled, the system has already recycled those log segments you are interested in many hundred times over. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dump large DB and restore it after all.
* Condor wrote: Problem was when I start: pg_dump dbname | split -b 1G - filenameI unable to restore it correct. When I start restore DB i got error from sql he did not like one line. I make investigation and the problem was in last line of first file value field was something like 'This is a ' and here file over. I added single quotes in the example that I can pay the phrase to make it clear what I mean. In next file sentence was end correct 'simple test' (also without single quotes) and this was not psql does not know that you split the file. You must send the whole dump to psql at once, not piece by piece. Instead of psql dbname filename.1 psql dbname filename.2 do this: cat filename.1 filename.2 | psql dbname -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Location Data
* Adarsh Sharma wrote: Today I am facing a simple problem that I fail to solve after 2 day try. I have a places table in database whose structure is as : CREATE TABLE places ( woe_id character varying(15) NOT NULL, iso character varying(6), name text, language character varying(6), place_type character varying, parent_woe_id character varying(15), lat numeric(12,8), lon numeric(12,8) CONSTRAINT places_pkey PRIMARY KEY (woe_id) )WITH ( OIDS=FALSE); It's simple *name *column contains the name of places in a hierarchical order. fore.g *woe_id iso name language places_type parent_woe_id lat lon 1 ZZ Earth ENG Supername 0 13.3445 234.666 10 IN INDIA ENG Country 1 12.44 234.667 11 IN JK ENG State 10 4535.56 3453.77 12 IN Udhanput ENG District 11 1222 3443.8 15 IN Parth ENG Town 12 111.6 1222.5 *I hope U understand what i am trying to explain. Now I want this data in the same table in extra columns fore.g *woe_id iso name language places_type parent_woe_id lat lon town district state country 1 ZZ Earth ENG Supername 0 13.3445 234.666 10 IN INDIA ENG Country 1 12.44 234.667 11 IN JK ENG State 10 4535.56 3453.77 **INDIA* *12 IN Udhanput ENG District 11 1222 3443.8 **JK **INDIA* *15 IN Parth ENG Town 12 111.6 1222.5 **Udhanput **JK **INDIA* Write a set of functions to get the higher-level structures (country for states, etc.) for any given record, and put a trigger on the table that populates the fields on insert and update. Sort of a materialized view. If you have little query activity on the table, create a view that calls the functions. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Location Data
* Adarsh Sharma wrote:
Christian Ullrich wrote:
Write a set of functions to get the higher-level structures (country
for states, etc.) for any given record, and put a trigger on the table
that populates the fields on insert and update.
All the world data is populated in the places table. Now I don't think
insert occurs anymore now.
Then add the new fields to the table and update them from the function
results.
Sort of a materialized view. If you have little query activity on the
table, create a view that calls the functions.
yes a function is must needed for this problem, bt any idea about the
flow of the function.
Simple version:
create function get_ancestor(p_woe_id varchar, p_level varchar) returns
text language plpgsql as $$
declare
v_woe_id varchar;
v_name text;
v_place_type varchar;
v_parent varchar;
begin
select woe_id, place_type, parent_woe_id into v_woe_id, v_place_type,
v_parent from places where woe_id = p_woe_id;
if (not found or v_place_type = p_level) then
return null;
else
while (found and v_place_type != p_level) loop
select name, place_type, parent_woe_id into v_name,
v_place_type, v_parent from places where woe_id = v_parent;
end loop;
if found then
return v_name;
else
return null;
end if;
end if;
end;$$;
select get_ancestor('15', 'State') as state, get_ancestor('15',
'District') as district;
You could produce a more refined version using WITH RECURSIVE, but if
your table is already complete and all you need is to put in the
denormalized data, this will work just as well. Beware of cyclical
references.
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Drop CHECK Constraint
* Yan Cheng CHEOK wrote: I was wondering, is there any good way to drop a constraint? Currently, I am making assumption on the check constraint name. ALTER TABLE backup_table ADD CHECK (fk_lot_id = 99); If I want to drop the above CHECK constraint, I will do ALTER TABLE backup_table DROP CONSTRAINT backup_table_fk_lot_id_check; (I assume the constraint name will be backup_table_fk_lot_id_check) Is there any more robust way? Name your constraints: ALTER TABLE backup_table ADD CONSTRAINT lot_id_ck CHECK (fk_lot_id = 99) Alternatively, you can get a list of constraints for your table from the catalog: SELECT conname FROM pg_constraint WHERE conrelid = 'backup_table'::regclass You may have to add more conditions to the query. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multithreaded query onto 4 postgresql instances
* Alessandro Candini wrote: Il 14/02/2011 21:00, Allan Kamau ha scritto: On Mon, Feb 14, 2011 at 10:38 AM, Alessandro Candinicand...@meeo.it wrote: No, this database is on a single machine, but a very powerful one. Processors with 16 cores each and ssd disks. I already use partitioning and tablespaces for every instance of my db and I gain a lot with my splitted configuration. My db is pretty huge: 600 milions of records and partitioning is not enough... I performed tests with a query returning more or less 10 records and using my C module I obtain the following results (every test performed cleaning cache before): - single db: 9.555 sec - splitted in 4: 5.496 sec So your problem is that one query, which is executed by a single backend process, is too slow. You fixed that by spreading the data across four database clusters on the same machine, querying them in parallel and merging the results in the client. I think you may have thought too far out of the box here. What is the performance if you leave all the data in a single database, suitably partitioned, then open multiple connections to that same database and run as many queries as you need to query each partition at the same time? As others here, I can hardly imagine that a setup involving four postmasters with one active backend each can be faster than having a single postmaster with, say, four active backends. It's true that PostgreSQL cannot parallelize a single query. But it is very good at running multiple queries in parallel, especially if you can limit I/O and lock contention by matching queries to partitions. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using copy for WAL archiving on Windows
* Harald Armin Massa wrote:
Should PostgreSQL maybe provide its own file-copy utility for
Windows that meets the requirements for safe WAL archiving?
Microsoft does provide an enterprise-ready webscale copy program ... it
is called robocopy and part of the Windows Server Resource Kit Tools.
Would you mind trying that utility for copying WAL-files?
I'm not sure I trust _that_:
### archive_wal.bat ###
robocopy pg_xlog c:\wal %1 /r:0 /w:0 /xc
if errorlevel 4 exit 1
if errorlevel 1 exit 0
if errorlevel 0 exit 1
### end ###
archive_command = archive_wal.bat %f
robocopy's exit codes make good reading, if you are into horror stories.
Thanks for the tip; I had previously discounted robocopy because I
thought it could not copy individual files, only whole directories (with
exclusion patterns, but not inclusion patterns). It turns out I will not
use it for entirely different reasons.
The perfect archiving utility for Windows is something like this:
### archive_wal.c ###
#define UNICODE 1
#define _UNICODE 1
#include windows.h
int wmain(int argc, WCHAR *argv[])
{
return ((argc == 3
CopyFile(argv[1], argv[2], TRUE) != 0) ? 0 : 1);
}
### end ###
archive_command = archive_wal %p c:\wal\%f
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
* tuanhoanganh wrote: I download postgresql from Enterprise DB On Thu, Jan 20, 2011 at 6:06 AM, Christian Ullrich ch...@chrullrich.net mailto:ch...@chrullrich.net wrote: We cannot assume that the one-click installer was used, but if it was, the service account it creates will be a member of the Users group only. And, to confirm, when you did the copy yourself, you were logged on as the postgres user that was created during the installation? Then please recheck that the user the PostgreSQL service is running as does in fact have: - full control permissions for the source directory (data and all subdirs) - full control permissions for the target directory (WAL) - at least read and execute permissions for all directories from the D: root down to the PITR directory If you have enabled disk quotas, make sure that the postgres user has not reached the quota limit on the D: drive. Have you had any luck with xcopy instead of copy, or has procmon found out which operation actually fails? -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
* tuanhoanganh wrote: Here is procmon i thinks error [some procmon events] No, that is all OK. The event at 2:39:55.7588651 is where Postgres starts cmd.exe to perform the copy. The really interesting data would be from cmd.exe itself, which implements the copy command. Please send the events from cmd.exe for the ten seconds following that timestamp. If it is overly much, please send it to me directly. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
* tuanhoanganh wrote: I have changed archive_command to archive_command = 'copy %p D:\\3SDATABACKUP\\PITR\\WAL\\%f' and it work again. Argh. How could I not have seen that? But why old archive_command work from 01/01 to 05/01 archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f' It works with the forward slashes if the destination path is quoted, but it looks like you did not do that in any of your examples. Other than that, I have no idea. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
* tuanhoanganh wrote: My PITR work well from 01/01/2011 to 06/01/2011. At 06/01/2011 postgresql log have issue 2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E [... lots more ...] 2011-01-06 08:28:58 ICT WARNING: transaction log file 00010004005E could not be archived: too many failures And my pg_xlog can not copy to D:/3SDATABACKUP/PITR/WAL from 06/01/2011. How to fix error ? Please help me Some possible reasons: - Target disk full - PostgreSQL user does not have write privilege for the target directory - Target file exists already (then you have a bigger problem) - PostgreSQL user does not have full control privileges for the source file (the copy command needs them) If you can stop your server, do so, then try to copy the file yourself on the command line. If that fails as well, you will get a better error message. If the server must stay up, copy the file to somwhere else on D: (assuming you don't have a volume mounted somewhere in the path). If you can copy the file yourself, you have a permissions problem. Make sure the PostgreSQL service user has full control on both the source and target directories. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
* tuanhoanganh wrote: I have checked your solution. - Target disk full : No - PostgreSQL user does not have write privilege for the target directory : No - Target file exists already (then you have a bigger problem) : Last file in D:/3SDATABACKUP/PITR/WAL is 00010004005D - PostgreSQL user does not have full control privileges for the source file (the copy command needs them) : i switch to user postgres an copy 00010004005E from source to d:\temp and create new text file on D:/3SDATABACKUP/PITR/WAL it is ok. No access denied So when PostgreSQL runs copy 000...5E D:\..., it fails, and when you do the same thing as the PostgreSQL user, it works. Interesting. Try increasing the log level in postgresql.conf to see if it logs the error message from copy, or try xcopy instead of copy. Do you have some antivirus software on that computer? Make sure the PostgreSQL data directory and the backup directory are excluded. Run procmon http://technet.microsoft.com/en-us/sysinternals/bb896645 to see what copy tries to do when it fails. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using copy for WAL archiving on Windows
Hello all, the PostgreSQL manual, section 24.3.1, has an example archive_command for Windows. It is copy %p C:\\server\\archivedir\\%f . The next sentence disclaims this as an example, not a recommendation. I just had occasion to do some tests with that, and it appears to me that using copy for this is a really bad idea. As it turns out, copy (at least on Windows 7) does not return a non-zero exit code if the copy failed because the destination file already existed. C:\Datenecho. t C:\Datencopy t s 1 Datei(en) kopiert. C:\Datencopy t s s überschreiben? (Ja/Nein/Alle): n 0 Datei(en) kopiert. C:\Datenecho %ERRORLEVEL% 0 (It's in German, but I think the problem is obvious.) Next attempt, this time with no interactive prompt: C:\Datencopy t s nul s überschreiben? (Ja/Nein/Alle): 0 Datei(en) kopiert. C:\Datenecho %ERRORLEVEL% 0 xcopy, on the other hand, works: C:\Datenxcopy t s nul C:\Daten\s überschreiben (Ja/Nein/Alle)? ﳐ C:\Daten\s überschreiben (Ja/Nein/Alle)? C:\Datenecho %ERRORLEVEL% 2 I'm not sure what that thing is it printed after the prompt, but at least the exit code is good. copy produces good exit codes for other errors, such as when it does not have permission to write to the target directory. The only situation where it fails to fail is when you have an identically named file in the target directory already. Unfortunately, that is also the easiest mistake to make -- you copy the configuration from one server to another and forget to make that little change. Now two servers archive to the same shared directory, and neither notices. Should PostgreSQL maybe provide its own file-copy utility for Windows that meets the requirements for safe WAL archiving? -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
* Magnus Hagander wrote: On Wed, Jan 19, 2011 at 19:20, Christian Ullrichch...@chrullrich.net wrote: So when PostgreSQL runs copy 000...5E D:\..., it fails, and when you do the same thing as the PostgreSQL user, it works. Interesting. Try increasing the log level in postgresql.conf to see if it logs the error message from copy, or try xcopy instead of copy. Note thatn when PostgreSQL runs, it will shed any rights given through Administrators or Power Users group. So this is not an identical test. We cannot assume that the one-click installer was used, but if it was, the service account it creates will be a member of the Users group only. tuanhoanganh, what did you download to install Postgres? -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Group By Question
* Andrew E. Tegenkamp wrote: I have two tables and want to attach and return the most recent data from the second table. Table 1 has a counter ID and name. Table 2 has a counter ID, Reference (to Table 1 ID), Date, and Like. I want to do a query that gets each name and their most recent like. I have a unique key setup on likes for the reference and date so I know there is only 1 per day. I can do this query fine: SELECT test.people.id, test.people.name, test.likes.ref, MAX(test.likes.date) FROM test.people LEFT JOIN test.likes ON test.people.id = test.likes.ref GROUP BY test.people.id, test.people.name, test.likes.ref However, when I try to add in test.likes.id OR test.likes.likes I get an error that it has to be included in the Group By (do not want that) or has to be an aggregate function. I just want the value of those fields from whatever row it is getting the MAX(date) field. SELECT p.name, l.date, l.likes FROM people p LEFT JOIN (SELECT l1.ref, l1.date, l1.likes FROM likes l1 GROUP BY l1.ref, l1.date, l1.likes HAVING l1.date = (SELECT max(date) FROM likes WHERE ref = l1.ref)) l ON (p.id = l.ref); Or the newfangled way, replacing the inner subselect with a window: SELECT p.id, p.name, l.likes FROM people p LEFT JOIN (SELECT l1.ref, l1.likes, l1.date, max(l1.date) OVER (PARTITION BY ref) AS maxdate FROM likes l1) l ON (p.id = l.ref AND l.date = l.maxdate); On this dataset, the windowed version is estimated to be ~ 60% faster than the grouped one, and the actual execution time is ~ 20% lower. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install
* Dave Page wrote: Thats very odd, but it explains why things are going wrong - essentially, the prerequisites are being unpacked to: C:\Users\Administrator\AppData\Local But the installer expects to find them in: C:\Users\Administrator\Lokale Einstellungen\ Which is a link to the first folder. I (as the guy the wrote the original version of the installer) expect them to be in: C:\Users\Administrator\Lokale Einstellungen\Temp\ So, it sounds like there are two questions for me to figure out - why is the installer not able to follow the link and find the files (which is probably a question for BitRock), and why isn't it using the actual Temp subdirectory as it's supposed to. It can't follow the link because these links (actually, junctions) have ACLs that deny FILE_READ_DATA, which means you cannot enumerate the contents of the target directory through the link. See http://technet.microsoft.com/en-us/magazine/ee851567.aspx for an explanation of the ACLs. The OP indicates in his reply to your message that his %TEMP% path is C:\Users\ADMINI~1\LOKALE~1\Temp , which is using one of these junctions. This is definitely not the default value set when the Administrator profile is created during installation; that value would be C:\Users\Administrator\AppData\Local\Temp . I would recommend to change the user environment variables (TEMP and TMP) to the correct value and retry. Of course, even if it works, this does not answer two questions: 1. How did TEMP end up with this value? 2. Why does the installer use the wrong directory? There are two things I can think of with regard to 1. The more likely one is that there is some logon script or group policy that applies to the local Administrator account, which was written for XP clients and therefore uses XP paths. The other idea is that his system may have been upgraded from XP by way of Vista and somehow kept the old paths intact. As for 2, I suspect that somewhere in the installer, it walks down the path to the TEMP directory, and fails at the junction because it cannot read the contents of its target directory. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq (C++) - Insert binary data
* GOO Creations wrote:
This is what I have until now to insert data
char *query = insert into table1 (bytes) values ($1);
QByteArray chip = assignment of bytes;
const char *data = chip-data();
const char* params[]={data};
const int params_length[]={chip-length()};
const int params_format[]={1};
result = PQexecParams(mDatabase, query, 1, in_oid, params,
params_length, params_format, 0);
The first problem I have is that I'm not sure if const int
params_length[]={chip-length()}; is the correct way to provide the
length. Second of all, is this actually the correct way of doing it,
isn't there a beter way?
It is certainly the simplest way of doing it in plain libpq, as long as
you're using the binary format (which you are doing here). According to
the documentation, QByteArray::length() returns the number of bytes in
the array, so it is the correct size.
I'm not sure if the (internal) binary format of bytea is guaranteed not
to change in future versions of PostgreSQL. Currently, as you obviously
found out yourself, it's pretty simple -- no transformation at all, the
binary format is just the data.
To make sure that your application supports any changes in future
versions, you should consider using the hex text format instead. See
section 8.4.1 of the manual for version 9.0.
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Failed regression tests with 7.0.3 on Windows NT 4.0
* Tom Lane wrote on Monday, 2000-12-04: Christian Ullrich [EMAIL PROTECTED] writes: I've just gotten PostgreSQL 7.0.3 to build and actually run under WinNT 4.0, and the regression tests show two problems: one probably minor in horology (I suppose there's a wrong time zone somewhere), and one probably major in alter_table. I attached the diff output. The horology discrepancy looks like WinNT doesn't have daylight-savings info before 1970. Try adding [...] Apparently a cygwin bug? Evidently rename() forgets to set errno on failure. renamerel() is expecting to see errno = ENOENT when the file to be renamed doesn't exist. Thanks a lot! BTW: You're quite right. cygwin's newlib doesn't seem to bother with errno all that much in stdio. At first grep, it isn't even mentioned in half the stdio source files. -- Christian UllrichRegistrierter Linux-User #125183 "Sie können nach R'ed'mond fliegen -- aber Sie werden sterben"
[GENERAL] Failed regression tests with 7.0.3 on Windows NT 4.0
Hello! I've just gotten PostgreSQL 7.0.3 to build and actually run under WinNT 4.0, and the regression tests show two problems: one probably minor in horology (I suppose there's a wrong time zone somewhere), and one probably major in alter_table. I attached the diff output. I'm not very pleased with having to run PostgreSQL on NT, but these are the facts I can't change. So, keep up the good work! Yours, -- Christian UllrichRegistrierter Linux-User #125183 "Sie können nach R'ed'mond fliegen -- aber Sie werden sterben" *** expected/horology.out Tue Mar 21 05:59:14 2000 --- results/horology.outSun Dec 3 14:06:49 2000 *** *** 235,243 | Wed Mar 15 08:14:01 2000 PST | @ 34 years| Tue Mar 15 08:14:01 1966 PST | Sun Dec 31 17:32:01 2000 PST | @ 34 years| Sat Dec 31 17:32:01 1966 PST | Mon Jan 01 17:32:01 2001 PST | @ 34 years| Sun Jan 01 17:32:01 1967 PST ! | epoch| @ 5 mons 12 hours | Thu Jul 31 05:00:00 1969 PDT ! | epoch| @ 5 mons | Thu Jul 31 17:00:00 1969 PDT ! | epoch| @ 3 mons | Tue Sep 30 17:00:00 1969 PDT | epoch| @ 10 days | Sun Dec 21 16:00:00 1969 PST | epoch| @ 1 day 2 hours 3 mins 4 secs | Tue Dec 30 13:56:56 1969 PST | epoch| @ 5 hours | Wed Dec 31 11:00:00 1969 PST --- 235,243 | Wed Mar 15 08:14:01 2000 PST | @ 34 years| Tue Mar 15 08:14:01 1966 PST | Sun Dec 31 17:32:01 2000 PST | @ 34 years| Sat Dec 31 17:32:01 1966 PST | Mon Jan 01 17:32:01 2001 PST | @ 34 years| Sun Jan 01 17:32:01 1967 PST ! | epoch| @ 5 mons 12 hours | Thu Jul 31 04:00:00 1969 PST ! | epoch| @ 5 mons | Thu Jul 31 16:00:00 1969 PST ! | epoch| @ 3 mons | Tue Sep 30 16:00:00 1969 PST | epoch| @ 10 days | Sun Dec 21 16:00:00 1969 PST | epoch| @ 1 day 2 hours 3 mins 4 secs | Tue Dec 30 13:56:56 1969 PST | epoch| @ 5 hours | Wed Dec 31 11:00:00 1969 PST *** *** 1159,1165 | Sat 01 Jan 17:32:01 2000 PST | Sun 31 Dec 17:32:01 2000 PST | Mon 01 Jan 17:32:01 2001 PST ! | Thu 13 Jun 00:00:00 1957 PDT (67 rows) SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL; --- 1159,1165 | Sat 01 Jan 17:32:01 2000 PST | Sun 31 Dec 17:32:01 2000 PST | Mon 01 Jan 17:32:01 2001 PST ! | Thu 13 Jun 00:00:00 1957 PST (67 rows) SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL; *** *** 1247,1253 | 2000-01-01 17:32:01-08 | 2000-12-31 17:32:01-08 | 2001-01-01 17:32:01-08 ! | 1957-06-13 00:00:00-07 (67 rows) SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL; --- 1247,1253 | 2000-01-01 17:32:01-08 | 2000-12-31 17:32:01-08 | 2001-01-01 17:32:01-08 ! | 1957-06-13 00:00:00-08 (67 rows) SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL; *** *** 1335,1341 | 01/01/2000 17:32:01.00 PST | 31/12/2000 17:32:01.00 PST | 01/01/2001 17:32:01.00 PST ! | 13/06/1957 00:00:00.00 PDT (67 rows) SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL; --- 1335,1341 | 01/01/2000 17:32:01.00 PST | 31/12/2000 17:32:01.00 PST | 01/01/2001 17:32:01.00 PST ! | 13/06/1957 00:00:00.00 PST (67 rows) SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL; -- *** expected/alter_table.outTue Mar 14 23:06:55 2000 --- results/alter_table.out Sun Dec 3 14:10:54 2000 *** *** 99,274 -- VACUUM ANALYZE tenk1; ALTER TABLE tenk1 RENAME TO ten_k; -- 20 values, sorted SELECT unique1 FROM ten_k WHERE unique1 20; ! unique1 ! - !0 !1 !2 !3 !4 !5 !6 !7 !8 !9 ! 10 ! 11 ! 12 ! 13 ! 14 ! 15 ! 16 ! 17 ! 18 ! 19 ! (20 rows) ! -- 20 values, sorted SELECT unique2 FROM ten_k WHERE unique2 20; ! unique2 ! - !0 !1 !2 !3 !4 !5 !6 !7 !8 !9 ! 10 ! 11 ! 12 ! 13 ! 14 ! 15 ! 16 ! 17 ! 18 ! 19 ! (20 rows) ! -- 100 values, sorted SELECT hundred FROM ten_k WHERE hundred = 50; ! hundred ! -
