pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMaintenance
14+ messages / 9 participants
[nested] [flat]
* Maintenance
@ 2000-04-17 11:25 Raul Carvalho <rmpc@fe.up.pt>
0 siblings, 1 reply; 14+ messages in thread
From: Raul Carvalho @ 2000-04-17 11:25 UTC (permalink / raw)
To: pgsql-admin
Hello all,
I am having a problem regarding maintenance of my databases. I have four
small db's and clients must use password autentication.
The problem is that when I try to pg_dump any of them, I don't know how
can I pass username and password. Shouldn't there be a command line option
to do this? Environment variables are very unconvenient...
The same problem regarding restoring the database. "cat xpto.dump | psql
-e dbname" also asks for passwd...
What solutions are you using? Please share them with me!
Thanks a lot,
Raul
Raul Miguel Pinheiro de Carvalho
ISR - Instituto de Sistemas e Robotica, Porto
e-mail: rmpc@fe.up.pt
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Maintenance
@ 2000-04-17 11:29 Maarten Boekhold <maarten.boekhold@tibcofinance.com>
parent: Raul Carvalho <rmpc@fe.up.pt>
0 siblings, 1 reply; 14+ messages in thread
From: Maarten Boekhold @ 2000-04-17 11:29 UTC (permalink / raw)
To: Raul Carvalho <rmpc@fe.up.pt>; +Cc: pgsql-admin
Raul Carvalho wrote:
>
> Hello all,
>
> I am having a problem regarding maintenance of my databases. I have four
> small db's and clients must use password autentication.
>
> The problem is that when I try to pg_dump any of them, I don't know how
> can I pass username and password. Shouldn't there be a command line option
> to do this? Environment variables are very unconvenient...
echo "username\npassword" | pg_dump -u ....
> The same problem regarding restoring the database. "cat xpto.dump | psql
> -e dbname" also asks for passwd...
echo "username\npassword" | psql -u -f xpto.dump ....
btw. if executing this from a script I find environment variables more
convenient.
Maarten
--
Maarten Boekhold, maarten.boekhold@tibcofinance.com
TIBCO Finance Technology Inc.
"Sevilla" Building
Entrada 308
1096 ED Amsterdam, The Netherlands
tel: +31 20 6601000 (direct: +31 20 6601066)
fax: +31 20 6601005
http://www.tibcofinance.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Maintenance
@ 2000-04-17 17:39 Raul Carvalho <rmpc@fe.up.pt>
parent: Maarten Boekhold <maarten.boekhold@tibcofinance.com>
0 siblings, 1 reply; 14+ messages in thread
From: Raul Carvalho @ 2000-04-17 17:39 UTC (permalink / raw)
To: Maarten Boekhold <maarten.boekhold@tibcofinance.com>; +Cc: pgsql-admin
that is exactly my point!
It gives this error: (database: demo, user: demo, password: demo)
$ echo "demo\ndemo" | pg_dump -u demo > r.dump
Connection to database 'demo' failed.
fe_sendauth: no password supplied
Very strange...
Raul Miguel Pinheiro de Carvalho
ISR - Instituto de Sistemas e Robotica, Porto
e-mail: rmpc@fe.up.pt
On Mon, 17 Apr 2000, Maarten Boekhold wrote:
>
>
> Raul Carvalho wrote:
> >
> > Hello all,
> >
> > I am having a problem regarding maintenance of my databases. I have four
> > small db's and clients must use password autentication.
> >
> > The problem is that when I try to pg_dump any of them, I don't know how
> > can I pass username and password. Shouldn't there be a command line option
> > to do this? Environment variables are very unconvenient...
>
> echo "username\npassword" | pg_dump -u ....
>
> > The same problem regarding restoring the database. "cat xpto.dump | psql
> > -e dbname" also asks for passwd...
>
> echo "username\npassword" | psql -u -f xpto.dump ....
>
> btw. if executing this from a script I find environment variables more
> convenient.
>
> Maarten
>
> --
>
> Maarten Boekhold, maarten.boekhold@tibcofinance.com
> TIBCO Finance Technology Inc.
> "Sevilla" Building
> Entrada 308
> 1096 ED Amsterdam, The Netherlands
> tel: +31 20 6601000 (direct: +31 20 6601066)
> fax: +31 20 6601005
> http://www.tibcofinance.com
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Maintenance
@ 2000-04-17 19:20 Raul Carvalho <rmpc@fe.up.pt>
parent: Raul Carvalho <rmpc@fe.up.pt>
0 siblings, 1 reply; 14+ messages in thread
From: Raul Carvalho @ 2000-04-17 19:20 UTC (permalink / raw)
To: Maarten Boekhold <maarten.boekhold@tibcofinance.com>; +Cc: pgsql-admin
Oh, I also tryed some of these:
(echo "user\n"; echo "password\n") | pg_dump.....
echo "user\npassword\n" | .....
It doesn't seem to work, though...
Raul Miguel Pinheiro de Carvalho
ISR - Instituto de Sistemas e Robotica, Porto
e-mail: rmpc@fe.up.pt
On Mon, 17 Apr 2000, Raul Carvalho wrote:
>
> that is exactly my point!
>
> It gives this error: (database: demo, user: demo, password: demo)
>
> $ echo "demo\ndemo" | pg_dump -u demo > r.dump
> Connection to database 'demo' failed.
> fe_sendauth: no password supplied
>
> Very strange...
>
> Raul Miguel Pinheiro de Carvalho
> ISR - Instituto de Sistemas e Robotica, Porto
> e-mail: rmpc@fe.up.pt
>
> On Mon, 17 Apr 2000, Maarten Boekhold wrote:
>
> >
> >
> > Raul Carvalho wrote:
> > >
> > > Hello all,
> > >
> > > I am having a problem regarding maintenance of my databases. I have four
> > > small db's and clients must use password autentication.
> > >
> > > The problem is that when I try to pg_dump any of them, I don't know how
> > > can I pass username and password. Shouldn't there be a command line option
> > > to do this? Environment variables are very unconvenient...
> >
> > echo "username\npassword" | pg_dump -u ....
> >
> > > The same problem regarding restoring the database. "cat xpto.dump | psql
> > > -e dbname" also asks for passwd...
> >
> > echo "username\npassword" | psql -u -f xpto.dump ....
> >
> > btw. if executing this from a script I find environment variables more
> > convenient.
> >
> > Maarten
> >
> > --
> >
> > Maarten Boekhold, maarten.boekhold@tibcofinance.com
> > TIBCO Finance Technology Inc.
> > "Sevilla" Building
> > Entrada 308
> > 1096 ED Amsterdam, The Netherlands
> > tel: +31 20 6601000 (direct: +31 20 6601066)
> > fax: +31 20 6601005
> > http://www.tibcofinance.com
> >
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: Maintenance
@ 2000-04-18 00:48 Rainer Mager <rmager@vgkk.co.jp>
parent: Raul Carvalho <rmpc@fe.up.pt>
0 siblings, 2 replies; 14+ messages in thread
From: Rainer Mager @ 2000-04-18 00:48 UTC (permalink / raw)
To: pgsql-admin
I've always done it by:
pg_dump .... [enter]
<blindly type username [enter] password [enter]>
This works for me with the note that the dump file now has my username and
password at the top of it.
--Rainer
> -----Original Message-----
> From: pgsql-admin-owner@hub.org
> [mailto:pgsql-admin-owner@hub.org]On Behalf Of Raul Carvalho
> Sent: Tuesday, April 18, 2000 4:21 AM
> To: Maarten Boekhold
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Maintenance
>
>
>
> Oh, I also tryed some of these:
>
> (echo "user\n"; echo "password\n") | pg_dump.....
> echo "user\npassword\n" | .....
>
>
> It doesn't seem to work, though...
>
> Raul Miguel Pinheiro de Carvalho
> ISR - Instituto de Sistemas e Robotica, Porto
> e-mail: rmpc@fe.up.pt
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: Maintenance
@ 2000-04-18 09:44 Raul Carvalho <rmpc@fe.up.pt>
parent: Rainer Mager <rmager@vgkk.co.jp>
1 sibling, 0 replies; 14+ messages in thread
From: Raul Carvalho @ 2000-04-18 09:44 UTC (permalink / raw)
To: Rainer Mager <rmager@vgkk.co.jp>; +Cc: pgsql-admin
Not very nice, but it worked :)
Thanks,
Raul
Raul Miguel Pinheiro de Carvalho
ISR - Instituto de Sistemas e Robotica, Porto
e-mail: rmpc@fe.up.pt
On Tue, 18 Apr 2000, Rainer Mager wrote:
> I've always done it by:
>
> pg_dump .... [enter]
> <blindly type username [enter] password [enter]>
>
> This works for me with the note that the dump file now has my username and
> password at the top of it.
>
> --Rainer
>
>
>
> > -----Original Message-----
> > From: pgsql-admin-owner@hub.org
> > [mailto:pgsql-admin-owner@hub.org]On Behalf Of Raul Carvalho
> > Sent: Tuesday, April 18, 2000 4:21 AM
> > To: Maarten Boekhold
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Maintenance
> >
> >
> >
> > Oh, I also tryed some of these:
> >
> > (echo "user\n"; echo "password\n") | pg_dump.....
> > echo "user\npassword\n" | .....
> >
> >
> > It doesn't seem to work, though...
> >
> > Raul Miguel Pinheiro de Carvalho
> > ISR - Instituto de Sistemas e Robotica, Porto
> > e-mail: rmpc@fe.up.pt
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: Maintenance
@ 2000-04-18 10:58 Raul Carvalho <rmpc@fe.up.pt>
parent: Rainer Mager <rmager@vgkk.co.jp>
1 sibling, 0 replies; 14+ messages in thread
From: Raul Carvalho @ 2000-04-18 10:58 UTC (permalink / raw)
To: Rainer Mager <rmager@vgkk.co.jp>; +Cc: pgsql-admin
How about restoring the database if it has a password?
TIA,
Raul
Raul Miguel Pinheiro de Carvalho
ISR - Instituto de Sistemas e Robotica, Porto
e-mail: rmpc@fe.up.pt
On Tue, 18 Apr 2000, Rainer Mager wrote:
> I've always done it by:
>
> pg_dump .... [enter]
> <blindly type username [enter] password [enter]>
>
> This works for me with the note that the dump file now has my username and
> password at the top of it.
>
> --Rainer
>
>
>
> > -----Original Message-----
> > From: pgsql-admin-owner@hub.org
> > [mailto:pgsql-admin-owner@hub.org]On Behalf Of Raul Carvalho
> > Sent: Tuesday, April 18, 2000 4:21 AM
> > To: Maarten Boekhold
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Maintenance
> >
> >
> >
> > Oh, I also tryed some of these:
> >
> > (echo "user\n"; echo "password\n") | pg_dump.....
> > echo "user\npassword\n" | .....
> >
> >
> > It doesn't seem to work, though...
> >
> > Raul Miguel Pinheiro de Carvalho
> > ISR - Instituto de Sistemas e Robotica, Porto
> > e-mail: rmpc@fe.up.pt
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Maintenance
@ 2024-05-08 09:17 Sunil Jadhav <sunilbjpatil@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Sunil Jadhav @ 2024-05-08 09:17 UTC (permalink / raw)
To: pgsql-admin
Hello team,
We have a 12TB db and one table having around 7 TB data , is there any
option we can removed the dead tuple and size also increase for that
partition without downtime .
Vaccum full we can't do because of
exclusive lock in tables during processing.
We have critical application so don't get the downtown so how to achieve
this please let us know
Thank you
Sunil
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Maintenance
@ 2024-05-08 09:31 Wasim Devale <wasimd60@gmail.com>
parent: Sunil Jadhav <sunilbjpatil@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Wasim Devale @ 2024-05-08 09:31 UTC (permalink / raw)
To: Sunil Jadhav <sunilbjpatil@gmail.com>; +Cc: pgsql-admin
Hi run vacuum only not full vaccum. And create a table using this existing
table, partition it and then rename it to original table.
On Wed, 8 May, 2024, 2:48 pm Sunil Jadhav, <sunilbjpatil@gmail.com> wrote:
> Hello team,
>
> We have a 12TB db and one table having around 7 TB data , is there any
> option we can removed the dead tuple and size also increase for that
> partition without downtime .
> Vaccum full we can't do because of
> exclusive lock in tables during processing.
>
> We have critical application so don't get the downtown so how to achieve
> this please let us know
>
>
> Thank you
> Sunil
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Maintenance
@ 2024-05-08 12:02 Thomas Kellerer <shammat@gmx.net>
parent: Sunil Jadhav <sunilbjpatil@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Thomas Kellerer @ 2024-05-08 12:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Sunil Jadhav schrieb am 08.05.2024 um 11:17:
> We have a 12TB db and one table having around 7 TB data , is there
> any option we can removed the dead tuple and size also increase for
> that partition without downtime . Vaccum full we can't do because of
> exclusive lock in tables during processing.
>
> We have critical application so don't get the downtown so how to
> achieve this please let us know
Have a look at pg_squeeze or pg_repack but both will create a copy of the table,
so if you don't have enough free disk space neither is an option.
You probably also want to investigate if making autovacuum more aggressive
helps
Did you validate that those dead tuples you see really are a problem and
are not re-used?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Maintenance
@ 2024-05-08 13:10 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 1 reply; 14+ messages in thread
From: Ron Johnson @ 2024-05-08 13:10 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, May 8, 2024 at 8:02 AM Thomas Kellerer <shammat@gmx.net> wrote:
[snip]
> Did you validate that those dead tuples you see really are a problem and
> are not re-used?
>
Don't dead tuples have to be vacuumed away before the space can be reused?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Maintenance
@ 2024-05-08 14:28 vrms <vrms@netcologne.de>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: vrms @ 2024-05-08 14:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/8/24 3:10 PM, Ron Johnson wrote:
> Don't dead tuples have to be vacuumed away before the space can be
reused?
I think that is correct. As per my understanding ...
VACUUM
- removes dead tuples and makes the consumed space available for
future data
- does not free disk space
- does not cause any locks
VACUUM FULL
- removes dead tuples and frees actual disk space
- causes locks on the table being VACUUMed
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Maintenance
@ 2024-05-08 14:31 Wasim Devale <wasimd60@gmail.com>
parent: vrms <vrms@netcologne.de>
0 siblings, 1 reply; 14+ messages in thread
From: Wasim Devale @ 2024-05-08 14:31 UTC (permalink / raw)
To: vrms <vrms@netcologne.de>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
vrms you are correct.
On Wed, 8 May, 2024, 7:59 pm vrms, <vrms@netcologne.de> wrote:
>
> On 5/8/24 3:10 PM, Ron Johnson wrote:
>
> > Don't dead tuples have to be vacuumed away before the space can be
> reused?
>
> I think that is correct. As per my understanding ...
>
> VACUUM
>
> - removes dead tuples and makes the consumed space available for future
> data
> - does not free disk space
> - does not cause any locks
>
> VACUUM FULL
>
> - removes dead tuples and frees actual disk space
> - causes locks on the table being VACUUMed
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Maintenance
@ 2024-05-08 15:13 Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
parent: Wasim Devale <wasimd60@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-05-08 15:13 UTC (permalink / raw)
To: Wasim Devale <wasimd60@gmail.com>; vrms <vrms@netcologne.de>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Tuples are not deleted. They are zero’d out and the space becomes available as free tuple space.
I would research your vacuum stats and think to change any auto-vacuum settings per table via ALTER TABLE command AFTER you can vacuum the entire db without performance degradation.
I would not vacuum a 4TB at once. I would chunk it out over schemas, etc. once that is done, vacuum db regularly as needed or set up cron jobs to vacuum the heavy hitter tables.
Adjusting the autovacuum auto-scale too low, 3-4 places right of the decimal, can have performance degradation.
After your maintenance, to reclaim linux space (if wanted), you have to backup, DROP db, then CREATE db, reload backup.
If you are on an LVM, you may want to look at those settings too.
From: Wasim Devale <wasimd60@gmail.com>
Sent: Wednesday, May 8, 2024 7:31 AM
To: vrms <vrms@netcologne.de>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Maintenance
vrms you are correct.
On Wed, 8 May, 2024, 7:59 pm vrms, <vrms@netcologne.de<mailto:vrms@netcologne.de>> wrote:
On 5/8/24 3:10 PM, Ron Johnson wrote:
> Don't dead tuples have to be vacuumed away before the space can be reused?
I think that is correct. As per my understanding ...
VACUUM
- removes dead tuples and makes the consumed space available for future data
- does not free disk space
- does not cause any locks
VACUUM FULL
- removes dead tuples and frees actual disk space
- causes locks on the table being VACUUMed
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2024-05-08 15:13 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-17 11:25 Maintenance Raul Carvalho <rmpc@fe.up.pt>
2000-04-17 11:29 ` Maarten Boekhold <maarten.boekhold@tibcofinance.com>
2000-04-17 17:39 ` Raul Carvalho <rmpc@fe.up.pt>
2000-04-17 19:20 ` Raul Carvalho <rmpc@fe.up.pt>
2000-04-18 00:48 ` Rainer Mager <rmager@vgkk.co.jp>
2000-04-18 09:44 ` Raul Carvalho <rmpc@fe.up.pt>
2000-04-18 10:58 ` Raul Carvalho <rmpc@fe.up.pt>
2024-05-08 09:17 Maintenance Sunil Jadhav <sunilbjpatil@gmail.com>
2024-05-08 09:31 ` Wasim Devale <wasimd60@gmail.com>
2024-05-08 12:02 ` Thomas Kellerer <shammat@gmx.net>
2024-05-08 13:10 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-08 14:28 ` vrms <vrms@netcologne.de>
2024-05-08 14:31 ` Wasim Devale <wasimd60@gmail.com>
2024-05-08 15:13 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox