Jeremy Tunnell | 1 Jun 02:50 2010
Picon

Re: Strange behaviour: create database hangs (v 8.4.4)

unregister

On Mon, May 31, 2010 at 9:59 AM, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
> Dale Gallagher <dale.gallagher <at> gmail.com> writes:
>> On 31 May 2010 13:10, Dale Gallagher <dale.gallagher <at> gmail.com> wrote:
>>> I am now using strace on postgres startup and watching the filesystem
>>> during the creation of the database. So far, it seems that files in
>>> PGHOME/base/1234/ are appearing, but VERY slowly. Thus far it's taken
>>> 7 minutes and 92 files have been created. The command has not
>>> completed within psql....
>>>
>>> Could this be a filesystem corruption issue? The host is using ext4.
>
>> The command finally completed. It took a VERY long time. Something is
>> wrong, but I have no clue what.
>
> Seems like you must have a kernel/filesystem issue, but I don't know
> what either.  CREATE DATABASE just copies the contents of template1
> using some absolutely straightforward code (look into src/port/copydir.c)
> so it's entirely filesystem-limited.
>
> You could try tracing the backend that's doing the command with
> "strace -r -p <pid>" to try to get an idea of which system calls
> are eating all the time.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
> To make changes to your subscription:
(Continue reading)

Tena Sakai | 1 Jun 03:36 2010
Picon

Re: What is field separator?

Hi Gabriele,

This isn’t strictly a psql problem, but please humor me.

I can do below from linux prompt:
  $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
 > psql -P 'format=unaligned' -P 'fieldsep=,' -t -f - musket       
And get:
  RS3094315,0.578121,0.735
  RS3094315,0.578121,0.265
  RS3115850,0.365978,0.251
  RS3115850,0.365978,0.749
  RS12562034,,0.135
  RS12562034,,0.865
  RS4475691,0.247133,0.710
  RS4475691,0.247133,0.290
  RS28705211,,0.725
  RS28705211,,0.275

But when I do this:
  $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
 > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
(here the only change from the previous is the specification of fieldsep)
Then I get:
  RS3094315\t0.578121\t0.735
  RS3094315\t0.578121\t0.265
  RS3115850\t0.365978\t0.251
  RS3115850\t0.365978\t0.749
  RS12562034\t\t0.135
  RS12562034\t\t0.865
  RS4475691\t0.247133\t0.710
  RS4475691\t0.247133\t0.290
  RS28705211\t\t0.725
  RS28705211\t\t0.275

What's denoted as \t is not a tab character.  It is a two character
sequence backslash followd by a t.  I have tried all other possibilities
such as \\t , "\t", etc that I can think of, but I cannot get an honest to
god tab character to appear as field separator.  I can do it from psql
and achieve what I want, but I need to do this from shell level.  Any
suggestion what else I can try?

Regards,

Tena Sakai
tsakai <at> gallo.ucsf.edu

On 5/30/10 1:37 AM, "Gabriele Bartolini" <gabriele.bartolini <at> 2ndQuadrant.it> wrote:

> Tena Sakai ha scritto:
>> Can somebody please tell me what “|” is called in my example above?
>> And is it possible to change it from “|” to something else for
>> display purpose?
> The field separatore in psql is used only when the format is "unaligned"
> (which does not happen to be default).
>
> In order for it to be effective, you need to type in psql:
>
> \pset format unaligned
> \pset fieldsep '\t'
>
> This will give you an output with columns that are separated by tab.
>
> However, I suggest that you look at the documentation for more options
> and ways to achive the same results:
> http://www.postgresql.org/docs/current/static/app-psql.html
>
> Cheers,
> Gabriele
Greg Smith | 1 Jun 05:07 2010

Re: What is field separator?

Tena Sakai wrote:
>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>  > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
> What's denoted as \t is not a tab character.  It is a two character
> sequence backslash followd by a t.  I have tried all other possibilities
> such as \\t , "\t", etc that I can think of, but I cannot get an honest to
> god tab character to appear as field separator. 

Yeah, that's a tough one.  In bash you can use ANSI C quoting to pull 
this off:

psql -c "select name,setting from pg_settings limit 1" -d postgres -At 
-F $'\t'

See http://wiki.bash-hackers.org/syntax/quoting for more information.  
Portability to other shells I'm not sure about.

Note that I did two other things a little differently than your example, 
as well as tightening up the abbreviations:

-Putting the command in -c " " is just better all around than piping 
into psql using echo.  It even works with multi-line input, i.e.:

psql -c "
select 1
"

-Explicitly specifying the database manually using -d makes what's 
happening easier to follow than expecting people to know the convention 
that the first non-option passed to psql is a database name.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg <at> 2ndQuadrant.com   www.2ndQuadrant.us

--

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

Alvaro Herrera | 1 Jun 05:58 2010

Re: What is field separator?

Excerpts from Tena Sakai's message of lun may 31 21:36:41 -0400 2010:

> But when I do this:
>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>  > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
> (here the only change from the previous is the specification of fieldsep)
> Then I get:
>   RS3094315\t0.578121\t0.735

Use a literal tab.  You can enter this in decent shells with Ctrl-V followed by a tab.

-- 
Álvaro Herrera <alvherre <at> commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--

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

Tena Sakai | 1 Jun 07:27 2010
Picon

Re: What is field separator?

Many thanks, Greg!

I got it to work by emulating what you wrote.
But I think you meant -A, not -At below.
>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>> -F $'\t'

Regards,

Tena Sakai
tsakai <at> gallo.ucsf.edu

On 5/31/10 8:07 PM, "Greg Smith" <greg <at> 2ndquadrant.com> wrote:

> Tena Sakai wrote:
>>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>>> psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
>> What's denoted as \t is not a tab character.  It is a two character
>> sequence backslash followd by a t.  I have tried all other possibilities
>> such as \\t , "\t", etc that I can think of, but I cannot get an honest to
>> god tab character to appear as field separator.
> 
> Yeah, that's a tough one.  In bash you can use ANSI C quoting to pull
> this off:
> 
> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
> -F $'\t'
> 
> See http://wiki.bash-hackers.org/syntax/quoting for more information.
> Portability to other shells I'm not sure about.
> 
> Note that I did two other things a little differently than your example,
> as well as tightening up the abbreviations:
> 
> -Putting the command in -c " " is just better all around than piping
> into psql using echo.  It even works with multi-line input, i.e.:
> 
> psql -c "
> select 1
> "
> 
> -Explicitly specifying the database manually using -d makes what's
> happening easier to follow than expecting people to know the convention
> that the first non-option passed to psql is a database name.

--

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

Tena Sakai | 1 Jun 07:29 2010
Picon

Re: What is field separator?

Thank you, Alvaro.

Literal tab, that's one thing I never tried.
Yes, it works.

Regards,

Tena Sakai
tsakai <at> gallo.ucsf.edu

On 5/31/10 8:58 PM, "Alvaro Herrera" <alvherre <at> commandprompt.com> wrote:

> Excerpts from Tena Sakai's message of lun may 31 21:36:41 -0400 2010:
> 
>> But when I do this:
>>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>>> psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
>> (here the only change from the previous is the specification of fieldsep)
>> Then I get:
>>   RS3094315\t0.578121\t0.735
> 
> Use a literal tab.  You can enter this in decent shells with Ctrl-V followed
> by a tab.

--

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

Dale Gallagher | 1 Jun 13:17 2010
Picon

Re: Strange behaviour: create database hangs (v 8.4.4)

On 31 May 2010 18:59, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
> Seems like you must have a kernel/filesystem issue, but I don't know
> what either.  CREATE DATABASE just copies the contents of template1
> using some absolutely straightforward code (look into src/port/copydir.c)
> so it's entirely filesystem-limited.

Thanks Tom. I've confirmed that it's not related to PostgreSQL, as
installing this version on another system has zero issues. I've posted
the issue to a Slackware specific forum:

http://www.linuxquestions.org/questions/slackware-14/slackware-13-sluggish-system-possibly-filesystem-811442/

--

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

ALEXANDER JOSE | 2 Jun 05:26 2010
Picon

Restore DataBase

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


Alexander Angel
Venezuela

Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! Try it!
Greg Smith | 2 Jun 08:02 2010

Re: What is field separator?

Tena Sakai wrote:
> I got it to work by emulating what you wrote.
> But I think you meant -A, not -At below.
>   
>>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>>> -F $'\t'
>>>       

You had -t in your original to turn off the display of column names and 
the counts at the end, and -A is the shortcut for what you had as "-P 
'format=unaligned'".  I throw "-At" into almost every use of psql from a 
bash script I do, that's the usual combination that gets the basic 
format to be right; then tweak things like the field separator afterwards.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg <at> 2ndQuadrant.com   www.2ndQuadrant.us

--

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

Szymon Guz | 2 Jun 08:07 2010
Picon

Re: Restore DataBase



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


Alexander Angel
Venezuela


How did you make the backup?


regards
Szymon Guz

Gmane