Messages posted by: ravalon_tin_it

Hello, i have some problems with Postgresql station where i cannot complete the pg_dumpall command...

I obtain ever message which say one or another table cannot be back-up by user XXX ...sometimes template1, someothers another tabel or database....

i tryied also with admin user....
I enter in the machine with administrator user, open DOS shell with CMD and after that i user the command for autenticate with special user....

runas /user:postgres "cmd.exe"

here i try to run pg_dumpall and i obtain ever error messages...

I would ask you what and where i have to control for delete the error message and complete the backup with pg_dumpall command...

i don't understand if i need to manual upgrade....

i could read "If you are using a version-specific installation directory, e.g. /opt/PostgreSQL/8.4, you do not need to move the old cluster. The one-click installers all use version-specific installation directories."

I use the windows .exe installation with installer, i have the 8.4.x version installed in c:\programmi\postgresql\8.4 directory... need i to upgrade manually ???

ehm......could you write me an example please ?? i'm not practice in this manual operation....

Hello, i read 9.0 release announcements... in release notes i could read some difficult for upgade from 8.4 version....

Some notes say that "=>" are deprecated and other differences... i read many steps for upgrade as use Pg_upgrade or change manually many parameters...stop server, copy data ecc.ecc...

The installer cannot do alone the upgrade ?

Someone could explain me the step for a secure upgrade ??

Thanks

Hello again...

my pgAgent run now...but the backup are maked only one time...after that they don't update the old backup file...

i used this code for the command

c:\programmi\postgresql\8.4\bin\pg_dump -i -h localhost -p 5432 -U eliosuser -w -E sql_ascii -F c -b -v -f "c:\BaKelios.backup" elios
c:\programmi\postgresql\8.4\bin\pg_dump -U eliosuser elios > C:\BakElios.sql
c:\programmi\postgresql\8.4\bin\pg_dumpall -U postgres > C:\BakEliosAll.sql

The password are stored in pgpass.conf file...

They run if are not file backup in the destination directory, but after that nothing happen...

Have i write other code for overwrite permission ???

And so how can i give permission to my friends which have low permission for restart server in crash case ?

I would not give high permission for all but only for restart server...

i my mind, i have this idea.....create a bacth file (.bat) which could restart server but i don't know which script use that implement a password setting.....do you think could exsist ?

in your advice which is the best way without create a dangerous situation where alla limited user could make a damage ??

Thanks again....so i will stop server and after i'll launch the installer.....

First i will make a dump for security reason....

Thanks for your suggests Scottiebo...

but the replacement option of binaries....in which way ? I cannot use the setup installer ?
Have i to update only the BIN directory manually ...or how ?

If i make a backup of database and after i use the normal installer and again i replace the dump file is not functional ??

Hello...i see is out the new versione 8.4.2.1

I use PostgreSQL in my agency server and run on 8.4.1.1 version perfectly....

...so, if i will go to update....may i have problems with pg_hba.conf or pgpass.conf files ??

Which is the best way for upgrade postgreSQL ? run the new version over the old version installed ?

Ok thanks....

i used pgpass.conf and added user and password for a specific database, so my .BAT file run perfect !

Thanks

I'm trying to create a .BAT file to use with Windows scheduler option...

this is the contento of .bat file

c:\programmi\postgresql\8.4\bin\pg_dump -i -h localhost -p 5432 -U userbase -E sql_ascii -F c -b -v -f "c:\db_backup.backup" databasename

But it need a password...

because of i create this for use in automation scheduling, how can i do ?? Where have i to write the password ?

I tryed the "-w" option but don't work

Ok thanks for now.....i'll try your suggest.....

But have i to copy this line as is in SQL statement of pgAgent scheduling job ??

My connection require password.....so, have i to set which pgpass.conf file ? the file in Admin User directory or the file in Postgres User directory ?

Hello,

i installed pgAgent on my postgresql agency server but i have no idea about how make a scheduled backup of my db.

I search in internet and i found only one sql example script but it don't run....

Here my first question.
1) which is a simple sql script for make a backup of specific database ?
....and could i do this also with a batch script ?

2) I think my sql script tested for now wna't run because i have not configured pgpass.conf file....
...i could see exist more of this file... which file i have to configure, the administrator pgpass file, or postgres pgpass ?
How have i to set this file ???
i found this example......

localhost:5432:postgres:postgres:postgres...

what mean exactly ?

Hello...

i need to put in a windows desktop a link for restart postgresql server for a limited account....in this way also who is not administrator could restart the server in special condition.

i copied the link in a desktop but i obtain an authorization error ....

Exsist a way for do this ?

yes i have....

now pgAgent work as service but job wnat start...

i have to configure pgpass.conf file

something like this

localhost:5432:postgres:postgres:postgres

what mean ? wich is the sequenze of values ? is HOST

ORT:...and then ?

Antoher question....

I found more pgpass.conf file in different user directory.....which file have i to set, tha administrator user file or postgres user file ?

Ok, thank you very much !!!!!

....i hope not disturb you again

W PostgreSQL !!!

I have configured a server with postgresql using this command line in pg_hba.conf file

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host userdb user 172.16.53.0/24 md5
host userdb user 172.16.43.0/24 md5

# IPv6 local connections:
#host all all ::1/128 md5

Perfect for 1° line and 2° line, where i set a range of ip as you wrote me in past thread...

the 3° line work, but i want to set a specific ip address, as 172.16.43.128 and not 172.16.43.0/24..... but if i put a single ip, server wan't start...is because this is an error ?

How could i set a single IP ?

Secondary....

Is my configuration secure or i could make best ?
Have i to set some address in listen_address on postgresql.conf file ? if yes, how ???

Consider all password are upper and lower case, with number and character....
Data for connection is set in my client application crypted with AES and in postgreSQL i used MD5 method....

A last question if i could....

i have to set the lan for a pc where i'll go tu install PostgreSQL
This pc will put in my agency LAN with a dedicated ip 172.16.73.153

The others pc in the lan which wi'll have my client application installed, are in 172.16.53.xxx IP

Will i encounter some problems ? specially with lan password access or various pc will connect to my PostgreSQL installation without problem ?

ah ok, after ANALYZE it return this

explain select * from prodotti where prodottoattivo=1 and codsettore='MICROB'

'Bitmap Heap Scan on prodotti (cost=5.54..28.05 rows=167 width=69)'
' Recheck Cond: ((codsettore)::text = 'MICROB'::text)'
' Filter: (prodottoattivo = 1)'
' -> Bitmap Index Scan on "indCodSettoreProdotti" (cost=0.00..5.50 rows=167 width=0)'
' Index Cond: ((codsettore)::text = 'MICROB'::text)'

i can see the usage of codsettore index, but not the prodottoattivo index....is right ?

in addiction, i have created a composite index which include also Codsettore and Prodottoattivo....why it is not used ??

i try also in this way

explain select * from prodotti where prodottoattivo=1 and codsettore='MICROB'

but nothing seem use index created...

i don't know if si case to put of Seq_scan....which is best ?

i've created for example a composite index for column PRODOTTOATTIVO e CODSETTORE

launching this query

explain select * from prodotti where prodottoattivo=1 and codsettore='MICROB'

i obtain this code...

'Seq Scan on prodotti (cost=0.00..30.35 rows=167 width=69)'
' Filter: ((prodottoattivo = 1) AND ((codsettore)::text = 'MICROB'::text))'

there are not references to the index used.... why ?

this is the structure of table PRODOTTI

CREATE TABLE prodotti
(
id integer NOT NULL DEFAULT nextval('"id_seq_Prodotti"'::regclass),
prodotto character varying(120) NOT NULL,
codprodotto character varying(20) NOT NULL,
codmagazzino character varying(20),
formato integer NOT NULL,
codfornitore integer NOT NULL,
idcategoria integer NOT NULL,
qtminima integer NOT NULL DEFAULT 0,
qtminimaordineaperto integer DEFAULT 0,
codsettore character varying(6) NOT NULL,
prodottoattivo smallint NOT NULL,
isscontomerce smallint NOT NULL,
CONSTRAINT prodotti_pkey PRIMARY KEY (id),
CONSTRAINT "FK_CategoriaProdotti" FOREIGN KEY (idcategoria)
REFERENCES categorieprodotti (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT "FK_Formati" FOREIGN KEY (formato)
REFERENCES formati (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT "FK_Fornitori" FOREIGN KEY (codfornitore)
REFERENCES fornitori (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
OIDS=FALSE
);
ALTER TABLE prodotti OWNER TO eliosuser;

-- Index: "indCodSettoreProdotti"

-- DROP INDEX "indCodSettoreProdotti";

CREATE INDEX "indCodSettoreProdotti"
ON prodotti
USING btree
(codsettore);

-- Index: "indIsScontoMerceProdotti"

-- DROP INDEX "indIsScontoMerceProdotti";

CREATE INDEX "indIsScontoMerceProdotti"
ON prodotti
USING btree
(isscontomerce);

-- Index: "indProdottoAttivoCodSettoreProdotti"

-- DROP INDEX "indProdottoAttivoCodSettoreProdotti";

CREATE INDEX "indProdottoAttivoCodSettoreProdotti"
ON prodotti
USING btree
(prodottoattivo, codsettore);

-- Index: "indProdottoAttivoProdotti"

-- DROP INDEX "indProdottoAttivoProdotti";

CREATE INDEX "indProdottoAttivoProdotti"
ON prodotti
USING btree
(prodottoattivo);

Oh yeah,....this was the problem !!!

I think at all but not at this connection from column and default value =sequence...

But....why pgAdmin III don't have a fast way for create this connection ? I have to put manually....

So..thank you very much !!!!!

P.S. Ashesh_V if i could ask, which is the tool do you use for paste code in this format ? i like it....

i obtain the errore reported in title.....

"NULL value in column ID violates not-null constraint .... "