Re: [GENERAL] how to use pg_dump and then restored onto development server
On 6/29/05, Douglas McNaught <[EMAIL PROTECTED]> wrote:
> "Zlatko Matic" <[EMAIL PROTECTED]> writes:
>
> > OK..i get it. It works...
> > My additional question is: how to incorporate timestamp in dumped file
> > name ?
> > Let's say, if we have script: pg_dump -f D:\MYDB_BCP -Fc -c -x -h
> > localhost -U postgres MYDB,
> > so that output file is named something like MYDB_BCP_2005-29-01, for
> > example. Is that possible?
>
> I'm sure it's possible, but I'm not an expert on Windows batch command
> language. There are some good references for that stuff on the
> web--check them out.
>
To get the date in a format that should work for you in win2000 and
winxp, open a command prompt and type the following command:
FOR /F "tokens=2-4 delims=/ " %f IN ('date /t') DO (echo %h-%g-%f)
To get the same date format inside a .bat batch script, replace each %
sign with two % signs, like this:
FOR /F "tokens=2-4 delims=/ " %%f IN ('date /t') DO (echo %%h-%%g-%%f)
You can run a similar command to get the current timestamp as well.
For more information on how to do this, and to better understand the
commands above, open a command prompt and type "for /?"... you will
see a help screen on the FOR construct in the windows command shell.
enjoy:)
Matt
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] *bsd port that installs the contribs?
> cd /usr/ports/databases/postgresql-contrib > make && make install && make clean > doh! Yet another obvious answer that escaped me, thanks. It is worth noting however, that this only applies to FreeBSD I don't see anything like it in OpenBSD. Free has a more comprehensive ports collection than Open though, so that's not a huge surprise. I don't have a NetBSD box to check for comparison. Despite me being blind, I think my other comment still holds true. ---(end of broadcast)--- TIP 3: 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] *bsd port that installs the contribs?
I there a way to specify that I want the contribs directory (and its children) compiled and installed during the build process in the various BSD ports systems? Of course I understand that what works on one BSD may not work on the others, but I don't see any FLAVORs or make options that would suggest there is a way to do this (I have looked in Open|Free ). Also as a minor complaint, I understand that the contribs aren't included in the default build for various reasons, but it would be nice to have some more information about them included in the documentation. I would expect some mention of them at least one of the postgresql-8.0.2/ README, INSTALL, configure, Makefile files, but there doesn't seem to be any. For example, searching the online docs for "contribs" "contribs -Release" or one of their members (ie "dbsize") comes up with release notes about how contribs/foo was updated or fixed, or how it might be used for testing something. I couldn't find any information online regarding how to build/install them short of downloading the source, unpacking, and reading the README files in the contrib directory. I can do this without too much trouble, but I expect that many people might not know where to look. Just a little nitpicking, but I figure if they're included in the release tarballs, there should probably be some more information on how to install them. Otherwise it's just wasted space :) Matt ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] psql vs perl prepared inserts
Thanks to all who replied. Thanks for the tip on that last thread Tom, I don't know how I missed it. I have a hunch that it's not applicable to me at this time because I'm running a year and a half old software (included in OpenBSD 3.4), but I will have to check which version of DBD::Pg was installed. > The intent of prepared statements is to reduce the overhead of running > the parser, rewriter and planner multiple times for a statement that is > executed multiple times. For an INSERT query without any sub-selects > that is not rewritten by any rules, the cost to parse, rewrite and plan > the statement is trivial. So I wouldn't expect prepared statements to be > a big win -- you would gain a lot more from batching multiple inserts > into a single transaction, and more still from using COPY. I was thinking something along the same lines, and was considering using the COPY statement as my next step, but as someone mentioned then I have to load it into a temporary database and then do some more internal magic to preserve referential integrity, unique contraints, etc. For that reason I was hoping to keep it in perl, and it's always nice to keep everything in a single neat portable package. Also, I forgot to mention earlier that I tried using transactions to speed things up, but since I expect to see certain inserts fail I would need to rework my code so the whole transaction doesn't fail if one insert goes bad. This is somewhat contrary to the purpose of transactions so I'm not sure how to accomplish this. I saw roughly a 20% speed improvement by turning autocommit off and only committing at the end of parsing each file. I think in the end I need to check the version of my Pg driver and perhaps upgrade to 7.4 or 8.0 in order to take advantage of the server side prepared statements. This is only a development box and I'm doing this mostly as an academic exercise that will someday help me speed up the production side, so upgrading isn't out of the question. Matt ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] psql vs perl prepared inserts
I've been experimenting with loading a large amount of data into a
fairly simple database using both psql and perl prepared statements.
Unfortunately I'm seeing no appreciable differences between the two
methods, where I was under the impression that prepared statements
should be much faster (in my case, they are slightly slower).
I have included a pseudocode/subset of the perl code I use below. You
can see the prepare statement outside the loop and the execute
statement inside the loop. Alternatively you can see that I write
every INSERT statement to a text file which I then load by using `psql
dbname -f bulksql_load.sql`. Normally I only have either the prepare
or the print-to-file in the loop, but i've included both to make the
pseudocode smaller.
Using a simple `time` command from my system it looks like the
execution time for loading all the data in both scenarios is about 50
seconds. FYI, the infile has 18k lines, and after parsing and the db
enforcing uniqueness there are 15.5k rows in the results table of the
db. This works out to ~300 inserts per second with on pgsql 7.3.2
with fsync turned off. I think that is a decent performance for this
old box, I'm just confused as to why the prepared statements don't
seem to give any speed boost as advertised.
Could the fact that many of my inserts have 15 columns slow down the
prepared statements to the point where they're no longer useful as a
speed enhancement? Or perhaps it's because I'm explicitly inserting
each field/value pair, even if many of them are null (which I think is
generally considered a good habit). Any other suggestions you might
have would be welcome. Please forgive me if I should have posted this
to pgsql-performance or some perl list, but I think the comparison
with psql makes it relevant.
Hopefully this won't be too messy:
#!/usr/bin/perl
open (IN,"scan.nbe");
open (OUT,">bulksql_load.sql");
use DBI;
$dbh = DBI->connect("dbi:Pg:dbname=nessus");
$sth_res = $dbh->prepare("INSERT INTO results
(scan_id,subnet,host,service,plugin_id,type,description,see_also,solution,risk_factor,cve_id,bid_id,port,proto,service_name)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$sth_tim = $dbh->prepare("INSERT INTO timestamps
(scan_id,hostip_or_nul,start_or_end,scan_time) VALUES (?,?,?,?)");
while (){
if (/^timestamps/){
parse_ts();
$sth_tim->execute($scan_id,$hostip_or_nul,$start_or_end,$scan_time);
print OUT "INSERT INTO timestamps
(scan_id,hostip_or_nul,start_or_end,scan_time) VALUES
($scan_id,$hostip_or_nul,$start_or_end,$scan_time)\n";
} elsif (/^results/) {
parse_res();
$sth_res->execute($scan_id,$subnet,$host,$service,$plugin_id,$type,$description,$see_also,$solution,$risk_factor,$cve_id,$bid_id,$port,$proto,$service_name);
print OUT "INSERT INTO results
(scan_id,subnet,host,service,plugin_id,type,description,see_also,solution,risk_factor,cve_id,bid_id,port,proto,service_name)
VALUES
($scan_id,$subnet,$host,$service,$plugin_id,$type,$description,$see_also,$solution,$risk_factor,$cve_id,$bid_id,$port,$proto,$service_name)\n";
}
}
---(end of broadcast)---
TIP 3: 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] Connection log entries - random port number recorded
On linux you can use "lsof -i" to see all current connections, I used to use something similar on >>Windows, but can't remember what it was. The tool you're looking for is called fport, and is distributed for free by Foundstone. It lists which program is using each open port. Alternatively you can just use netstat -an to just see which ports are actively communicating or listening, but it won't show you which program is using that port. Matt _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [HACKERS] COPY formatting
I have a comment about the following thread from about 2 months ago that dealt with delimiters in the copy command: http://marc.theaimsgroup.com/?l=postgresql-general&m=107960271207890&w=2 I wanted to chime in and say that I think having more complex delimiters would be very useful. I was curious if there are any developers considering working on this? Here is an example of why I would like to see multi character delimiters: I use pgsql 7.3.2 on OpenBSD 3.4 to store the results of my nessus scans (nessus is a very large and popular vulnerability scanner, see nessus.org). I currently use perl to parse the scan results and create tens of thousands of insert statements for each scan that is run, and was looking into using the copy command to speed up adding that data to the db. Nessus scan results are very text heavy and often have many characters within the text fields, so I don't think there are any single characters that I can use as a delimiter (^|,\n and many others are all used within the scan results). With that in mind I'd like to specify my own custom multi character delimiter to ensure that it won't be used within a field. Matt _ Best Restaurant Giveaway Ever! Vote for your favorites for a chance to win $1 million! http://local.msn.com/special/giveaway.asp ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] enforce unique rows?
I'm using v7.3.2 from OpenBSD 3.4 ports collection. I'm looking for a way to enforce unique rows in a table with about 20 columns. I found a way to do it, but it looks a little clunky so I was wondering if there was a better way. What I found is I have to do a: CREATE TABLE tblname (col1 text, col2 text, col3 text, UNIQUE(col1, col2, col3) ); In order for this command to work on a table with more columns, I have to include all column names individually within the UNIQUE() option. I would think I could say something more simple like: CREATE TABLE tblname (col1 text, col2 text, col3 text, UNIQUE(*) ); It just seemed natural to me to allow the use of a wildcard anywhere within a sql statement, but apparently that's not the case. Is there a reason the command can't be used in this fashion or maybe is there a better way to do it using another command? FYI the reason why I need uniqueness for entire rows is because sometimes when testing I've added the same data into the table more than once, and I'd like to prevent that. As long as I use the distinct option in select statements it hasn't been a problem, but enforcing unique rows is a more elegant solution. Also, the table in question currently has about 20 columns by 300,000 rows, just in case there might be any problems with keeping integrity on that many rows. Matt _ Best Restaurant Giveaway Ever! Vote for your favorites for a chance to win $1 million! http://local.msn.com/special/giveaway.asp ---(end of broadcast)--- TIP 3: 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
