Thread: Unnecessary files that can be deleted/moved in cluster dir?
I have a little problem, I let my drive get too full. And then while I was deleting rows to free space, the auto vacuumdidn't kick in quite the way I expected, and I ran out of space entirely. So the DB shut down and won't start backup. So is there anything ( other than the logs in pg_log) that I can delete, or move temporarily, to save some space and allowthe database to start up and finish it's vacuum? Or is there a way to move some of the stuff to another drive? The whole cluster is too big to move entirely to a new physicaldrive (the machine is in another city so I can't just plug in a USB drive or anything, but I can put stuff on networkshares for now) and there is nothing else on the partition other than the cluster. Thanks, -- John Abraham
On 4/01/2013 7:31 AM, John Abraham wrote: > I have a little problem, I let my drive get too full. And then while I was deleting rows to free space, the auto vacuumdidn't kick in quite the way I expected, and I ran out of space entirely. So the DB shut down and won't start backup. > > So is there anything ( other than the logs in pg_log) that I can delete, or move temporarily, to save some space and allowthe database to start up and finish it's vacuum? Not really. If your filesystem reserves 5% of space for root/emergency use, as ext3 and ext4 do by default, you can use tune2fs to set the reserve to zero. This should allow Pg to start up. At this point you can, if possible, use the COPY command or psql \copy command to get data from some tables to a location outside the full partition then DROP the tables. Older PostgreSQL versions had a VACUUM FULL that did space-recovery in-place, but it could be very slow and it still wouldn't work reliably in out-of-disk situations because the indexes could grow as the VACUUM proceeded. It also wouldn't do you any good if your disk was so full you couldn't even write new WAL. If you don't have a space reserve in the file system, you'll need to move the data directory to somewhere with more room or (if possible) expand the file system. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Jan 4, 2013 at 5:01 AM, John Abraham <jea@hbaspecto.com> wrote:
I have a little problem, I let my drive get too full. And then while I was deleting rows to free space, the auto vacuum didn't kick in quite the way I expected, and I ran out of space entirely. So the DB shut down and won't start back up.
What message it has written in DB server log file (pg_log) about it not starting again ?
You can check the last lines of the recent log file under $PG_DATA/pg_log location, which give very good information about why it has not started.
Hi,
My PostgreSQL is 9.2.1 in Ubuntu 12.04, I need to set up a connection pool by using pgbouncer.
I used "apt-get install pgbouncer", after configuring it, I can now connect to pgbouncer and can use all pgbouncer SHOW commands, however:
Q1) Is version "1.4.2/bouncer" the right one for PostgreSQL 9.2?
$ psql -U postgres -p 6543 pgbouncer
psql.bin (9.2.1, server 1.4.2/bouncer)
WARNING: psql.bin version 9.2, server version 1.4.
Some psql features might not work.
Type "help" for help.
No entry for terminal type "xterm-color";
using dumb terminal settings.
Q2) if I try the general psql commands, I got errors
for example:
$ psql -U postgres -p 6543 pgbouncer
pgbouncer=# \l
ERROR: invalid command 'SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;', use SHOW HELP;
Q3) I can connect to pgbouncer by using "psql -U postgres -p 6543 pgbouncer", however if I try to use "-d postgres", I got error:
$ psql -U postgres -p 6543 pgbouncer -d postgres
psql.bin: warning: extra command-line argument "pgbouncer" ignored
psql.bin: ERROR: no working server connection
Q4) Which port should I use in my application in order to connect to PostgreSQL via pgbouncer, port 6543 or port 5432?
the port value in pgbounce.ini:
postgres = port=5432 dbname=postgres
listen_port = 6543
the port value in postgresql.conf:
port=5432
Please help!
Thanks
On 08/01/2013 08:40, ac@hsk.hk wrote: > Hi, > > > My PostgreSQL is 9.2.1 in Ubuntu 12.04, I need to set up a connection > pool by using pgbouncer. > > > I used "apt-get install pgbouncer", after configuring it, I can now > connect to pgbouncer and can use all pgbouncer SHOW commands, however: > > Q1) Is version "1.4.2/bouncer" the right one for PostgreSQL 9.2? > $ psql -U postgres -p 6543 pgbouncer > psql.bin (9.2.1, server 1.4.2/bouncer) > WARNING: psql.bin version 9.2, server version 1.4. > Some psql features might not work. > Type "help" for help. > No entry for terminal type "xterm-color"; > using dumb terminal settings. > I think the best is the latest: v1.5.4 > > Q2) if I try the general psql commands, I got errors > for example: > $ psql -U postgres -p 6543 pgbouncer > pgbouncer=# \l > ERROR: invalid command 'SELECT d.datname as "Name", > pg_catalog.pg_get_userbyid(d.datdba) as "Owner", > pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", > pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges" > FROM pg_catalog.pg_database d > ORDER BY 1;', use SHOW HELP; If you connect to the pgbouncer, which is special, not really a database, you only can show pgbouncer stats and change pgbouncer settings # SHOW help; show you available commands. > > > Q3) I can connect to pgbouncer by using "psql -U postgres -p 6543 > pgbouncer", however if I try to use "-d postgres", I got error: > $ psql -U postgres -p 6543 pgbouncer -d postgres > psql.bin: warning: extra command-line argument "pgbouncer" ignored > psql.bin: ERROR: no working server connection You need to show us pgbouncer.ini. And maybe read this before http://get.enterprisedb.com/docs/Tutorial_All_PPSS_pgBouncer.pdf > > Q4) Which port should I use in my application in order to connect to > PostgreSQL via pgbouncer, port 6543 or port 5432? > the port value in pgbounce.ini: > postgres = port=5432 dbname=postgres > listen_port = 6543 > the port value in postgresql.conf: > port=5432 With this you make connection pooling only for the database named postgres. This is what you really want? Read this minihowto too: http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/ > > > > Please help! > Thanks > > >
Hi, Thanks for your reply. Below is the pgbouncer.ini === [databases] ; use db:postgres for connection testing postgres = port=5432 dbname=postgres [pgbouncer] listen_port = 6543 listen_addr = 127.0.0.1 admin_users = postgres auth_type = md5 auth_file = users.txt server_reset_query = DISCARD ALL; ignore_startup_parameters = application_name logfile = pgbouncer.log pidfile = pgbouncer.pid pool_mode = session default_pool_size = 20 log_pooler_errors = 0 === If I want to test the connection from my application to DB postgres via pgbouncer, which port should I use, post 6543 orport 5432? Thanks On 8 Jan 2013, at 3:13 PM, Birta Levente wrote: > On 08/01/2013 08:40, ac@hsk.hk wrote: >> Hi, >> >> >> My PostgreSQL is 9.2.1 in Ubuntu 12.04, I need to set up a connection >> pool by using pgbouncer. >> >> >> I used "apt-get install pgbouncer", after configuring it, I can now >> connect to pgbouncer and can use all pgbouncer SHOW commands, however: >> >> Q1) Is version "1.4.2/bouncer" the right one for PostgreSQL 9.2? >> $ psql -U postgres -p 6543 pgbouncer >> psql.bin (9.2.1, server 1.4.2/bouncer) >> WARNING: psql.bin version 9.2, server version 1.4. >> Some psql features might not work. >> Type "help" for help. >> No entry for terminal type "xterm-color"; >> using dumb terminal settings. >> > > I think the best is the latest: v1.5.4 > > >> >> Q2) if I try the general psql commands, I got errors >> for example: >> $ psql -U postgres -p 6543 pgbouncer >> pgbouncer=# \l >> ERROR: invalid command 'SELECT d.datname as "Name", >> pg_catalog.pg_get_userbyid(d.datdba) as "Owner", >> pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", >> pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges" >> FROM pg_catalog.pg_database d >> ORDER BY 1;', use SHOW HELP; > > If you connect to the pgbouncer, which is special, not really a database, you only can show pgbouncer stats and changepgbouncer settings > > # SHOW help; > show you available commands. > >> >> >> Q3) I can connect to pgbouncer by using "psql -U postgres -p 6543 >> pgbouncer", however if I try to use "-d postgres", I got error: >> $ psql -U postgres -p 6543 pgbouncer -d postgres >> psql.bin: warning: extra command-line argument "pgbouncer" ignored >> psql.bin: ERROR: no working server connection > > You need to show us pgbouncer.ini. > And maybe read this before > http://get.enterprisedb.com/docs/Tutorial_All_PPSS_pgBouncer.pdf > > >> >> Q4) Which port should I use in my application in order to connect to >> PostgreSQL via pgbouncer, port 6543 or port 5432? >> the port value in pgbounce.ini: >> postgres = port=5432 dbname=postgres >> listen_port = 6543 >> the port value in postgresql.conf: >> port=5432 > > With this you make connection pooling only for the database named postgres. This is what you really want? > > Read this minihowto too: > http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/ > >> >> >> >> Please help! >> Thanks >> >> >> > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
!! Please do not top post !! On 08/01/2013 10:21, ac@hsk.hk wrote: > Hi, > > Thanks for your reply. > > Below is the pgbouncer.ini > === > [databases] > ; use db:postgres for connection testing > postgres = port=5432 dbname=postgres > As I said before, with this you can only connect database postgres. Maybe you want: * = port=5432 With these you can connect all databases in your cluster. > [pgbouncer] > listen_port = 6543 > listen_addr = 127.0.0.1 watch this: maybe you want to connect from the outside: listen_addr = * > admin_users = postgres > auth_type = md5 > auth_file = users.txt I don't know if on ubuntu need or not specify the full path to the users.txt. > server_reset_query = DISCARD ALL; > ignore_startup_parameters = application_name > logfile = pgbouncer.log > pidfile = pgbouncer.pid > pool_mode = session > default_pool_size = 20 > log_pooler_errors = 0 > === > > If I want to test the connection from my application to DB postgres via pgbouncer, which port should I use, post 6543 orport 5432? The pooler is between client and postgresql server. So, if you want to connect through the pooler you need to connect on 6543. But I think you really need to read some documentation about pgbouncer. > > Thanks > > > > > On 8 Jan 2013, at 3:13 PM, Birta Levente wrote: > >> On 08/01/2013 08:40, ac@hsk.hk wrote: >>> Hi, >>> >>> >>> My PostgreSQL is 9.2.1 in Ubuntu 12.04, I need to set up a connection >>> pool by using pgbouncer. >>> >>> >>> I used "apt-get install pgbouncer", after configuring it, I can now >>> connect to pgbouncer and can use all pgbouncer SHOW commands, however: >>> >>> Q1) Is version "1.4.2/bouncer" the right one for PostgreSQL 9.2? >>> $ psql -U postgres -p 6543 pgbouncer >>> psql.bin (9.2.1, server 1.4.2/bouncer) >>> WARNING: psql.bin version 9.2, server version 1.4. >>> Some psql features might not work. >>> Type "help" for help. >>> No entry for terminal type "xterm-color"; >>> using dumb terminal settings. >>> >> >> I think the best is the latest: v1.5.4 >> >> >>> >>> Q2) if I try the general psql commands, I got errors >>> for example: >>> $ psql -U postgres -p 6543 pgbouncer >>> pgbouncer=# \l >>> ERROR: invalid command 'SELECT d.datname as "Name", >>> pg_catalog.pg_get_userbyid(d.datdba) as "Owner", >>> pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", >>> pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges" >>> FROM pg_catalog.pg_database d >>> ORDER BY 1;', use SHOW HELP; >> >> If you connect to the pgbouncer, which is special, not really a database, you only can show pgbouncer stats and changepgbouncer settings >> >> # SHOW help; >> show you available commands. >> >>> >>> >>> Q3) I can connect to pgbouncer by using "psql -U postgres -p 6543 >>> pgbouncer", however if I try to use "-d postgres", I got error: >>> $ psql -U postgres -p 6543 pgbouncer -d postgres >>> psql.bin: warning: extra command-line argument "pgbouncer" ignored >>> psql.bin: ERROR: no working server connection >> >> You need to show us pgbouncer.ini. >> And maybe read this before >> http://get.enterprisedb.com/docs/Tutorial_All_PPSS_pgBouncer.pdf >> >> >>> >>> Q4) Which port should I use in my application in order to connect to >>> PostgreSQL via pgbouncer, port 6543 or port 5432? >>> the port value in pgbounce.ini: >>> postgres = port=5432 dbname=postgres >>> listen_port = 6543 >>> the port value in postgresql.conf: >>> port=5432 >> >> With this you make connection pooling only for the database named postgres. This is what you really want? >> >> Read this minihowto too: >> http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/ >> >>> >>> >>> >>> Please help! >>> Thanks >>> >>> >>> >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >
On 8 Jan 2013, at 5:04 PM, Birta Levente wrote:
The pooler is between client and postgresql server. So, if you want to connect through the pooler you need to connect on 6543.
But I think you really need to read some documentation about pgbouncer.
Thanks, I can connect to any testing DB via pgbouncer now.
Regards
Hi, In PostgreSQL 9.0.x we must define a constraint as DEFERRABLE on the "create table", we cannot define DEFERRABLE on "createtable as select", how is this restriction in 9.2 now? Also, in 9.2 can deferrable uniqueness be mixed with Foreign keys? Thanks