Thread: pgsql data file location
All that is possible. I you have not read the Admin Docs yet. Regds mallah. > I am trying to find where psql reads the data from when a table is created. Also is it possible > to copy that data and transfer it onto another system running psql. If not does anyone know a > good psql backup solution. > > Thanks > > ismaila Kane ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
Hello Mallah, Tried the PG_dump command and it works great. Any idea about the location of the backup file?Also is the database backup or are the tables and schemas backed up too.Any restore command to be used . Thank you Ismaila Kane ----- Original Message ----- From: <mallah@trade-india.com> To: <ikane@ciscolabs.net> Cc: <pgsql-general@postgresql.com> Sent: Friday, April 11, 2003 2:17 PM Subject: Re: [GENERAL] pgsql data file location > > Dear Kane, > > you are rite it does not say explicitly the location of data file . > > But if you are wanting to know it for backup purposes reasons you are > in the wrong direction. > > The only way to make safe/consistent backups in postgresql is to use > pg_dump or pg_dumpall. > > If you are just curious about the location, it is either passed to postmaster > by -D option or by environment PGDATA. for typical tar ball installs it is > /usr/local/pgsql/data/ in RPM based it is /var/lib/pgsql/data > > under data folder there exists a folder "base" , under "base" one folder per database. > these folders have names correspoding to internal ids in system catalogs. > under these numeric folder there are files for individual tables and indexes. > > but do not copy these files and folder thinking that you are "backing up" > it will be futile , as said pg_dump/all is the only way of backing up. > > Regds > Mallah. > > > > > > > Yes I read the manual but does not say where the location of the data file is? > > ----- Original Message ----- > > From: <mallah@trade-india.com> > > To: <ikane@ciscolabs.net> > > Cc: <pgsql-general@postgresql.org> > > Sent: Friday, April 11, 2003 4:14 AM > > Subject: Re: [GENERAL] pgsql data file location > > > > > >> > >> All that is possible. > >> I you have not read the Admin Docs yet. > >> > >> Regds > >> mallah. > >> > >> > >> > I am trying to find where psql reads the data from when a table is > > created. Also is it possible > >> > to copy that data and transfer it onto another system running psql. If > > not does anyone know a > >> > good psql backup solution. > >> > > >> > Thanks > >> > > >> > ismaila Kane > >> > >> > >> > >> ----------------------------------------- > >> Get your free web based email at trade-india.com. > >> "India's Leading B2B eMarketplace.!" > >> http://www.trade-india.com/ > > > > ----------------------------------------- > Get your free web based email at trade-india.com. > "India's Leading B2B eMarketplace.!" > http://www.trade-india.com/ > >
pg_dumpall or pg_dump can dump data in multiple formats . By defualt they dump to SQL script files in text format. suppose you dump using the command $ pg_dumpall > dump.sql you can restore it as follows: $ psql -U<username> template1 -f dump.sql if you dump in custom format using -Fc option ie $ pg_dumpall -Fc > dump.dat you must use pg_restore and pipe the results to postgresql using psql eg $ pg_restore dump.dat | psql -U<username> template1 also if you are just wanting to quickly transfer the installation from one machine to another and postgresql version remaining the same you can directly copy the files this is how it can be done . >SHUTDOWN postmaster 2. cd /usr/local/pgsql/data or whatever applicable. 3. tar -cvf data.tar * 4. ftp data.tar to another machine. in another machine: assuming same version of pgsql is installed >SHUTDOWN postmaster if its running 5. cd /usr/local/pgsql 6. rename data folder , mv data data-bak 7. mkdir data 8. chown postgres: data 9. cd data 8. tar -xvf /path/to/data.tar start postmaster. PS: please care to cc to the list while replying by pressing "reply all" > Thank you.This is very helpful. Webmin has a backup/restore function but it does not work.I > just tested the dumpall command and it seems to work. How about restore function? > > Regards > > Ismaila Kane > > -----Original Message----- > From: mallah@trade-india.com [mailto:mallah@trade-india.com] > Sent: Fri 4/11/2003 2:17 PM > To: Ismaila Kane > Cc: pgsql-general@postgresql.com > Subject: Re: [GENERAL] pgsql data file location > > > > > Dear Kane, > > you are rite it does not say explicitly the location of data file . > But if you are wanting to know it for backup purposes reasons you are in the wrong > direction. > > The only way to make safe/consistent backups in postgresql is to use pg_dump or pg_dumpall. > > If you are just curious about the location, it is either passed to postmaster by -D option or > by environment PGDATA. for typical tar ball installs it is /usr/local/pgsql/data/ in RPM > based it is /var/lib/pgsql/data > > under data folder there exists a folder "base" , under "base" one folder per database. these > folders have names correspoding to internal ids in system catalogs. under these numeric > folder there are files for individual tables and indexes. > but do not copy these files and folder thinking that you are "backing up" it will be futile , > as said pg_dump/all is the only way of backing up. > Regds > Mallah. > > > > > > > Yes I read the manual but does not say where the location of the data file is? ----- > > Original Message ----- > > From: <mallah@trade-india.com> > > To: <ikane@ciscolabs.net> > > Cc: <pgsql-general@postgresql.org> > > Sent: Friday, April 11, 2003 4:14 AM > > Subject: Re: [GENERAL] pgsql data file location > > > > > >> > >> All that is possible. > >> I you have not read the Admin Docs yet. > >> > >> Regds > >> mallah. > >> > >> > >> > I am trying to find where psql reads the data from when a table is > > created. Also is it possible > >> > to copy that data and transfer it onto another system running psql. If > > not does anyone know a > >> > good psql backup solution. > >> > > >> > Thanks > >> > > >> > ismaila Kane > >> > >> > >> > >> ----------------------------------------- > >> Get your free web based email at trade-india.com. > >> "India's Leading B2B eMarketplace.!" > >> http://www.trade-india.com/ > > > > ----------------------------------------- > Get your free web based email at trade-india.com. > "India's Leading B2B eMarketplace.!" > http://www.trade-india.com/ > > > > ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
> Hello Mallah, > > Tried the PG_dump command and it works great. Any idea about the location of the backup > file? pg_dump is supposed to outout to stdout. you can redirect to a file using > $ pg_dump dbname > /path/to/dumpfile.sql Also is the database backup or are the tables and schemas backed up too. it backs up everything in the database except large objects(lo) (ignore it if u dont know understand lo) what else do u expect it to backup if not tables and schemas? Any restore > command to be used . $ psql -f dumpfile.sql > > Thank you > > Ismaila Kane > ----- Original Message ----- > From: <mallah@trade-india.com> > To: <ikane@ciscolabs.net> > Cc: <pgsql-general@postgresql.com> > Sent: Friday, April 11, 2003 2:17 PM > Subject: Re: [GENERAL] pgsql data file location > > >> >> Dear Kane, >> >> you are rite it does not say explicitly the location of data file . >> >> But if you are wanting to know it for backup purposes reasons you are in the wrong direction. >> >> The only way to make safe/consistent backups in postgresql is to use pg_dump or pg_dumpall. >> >> If you are just curious about the location, it is either passed to > postmaster >> by -D option or by environment PGDATA. for typical tar ball installs it is >> /usr/local/pgsql/data/ in RPM based it is /var/lib/pgsql/data >> >> under data folder there exists a folder "base" , under "base" one folder > per database. >> these folders have names correspoding to internal ids in system catalogs. under these numeric >> folder there are files for individual tables and > indexes. >> >> but do not copy these files and folder thinking that you are "backing up" it will be futile , >> as said pg_dump/all is the only way of backing up. >> >> Regds >> Mallah. >> >> >> >> >> >> > Yes I read the manual but does not say where the location of the data > file is? >> > ----- Original Message ----- >> > From: <mallah@trade-india.com> >> > To: <ikane@ciscolabs.net> >> > Cc: <pgsql-general@postgresql.org> >> > Sent: Friday, April 11, 2003 4:14 AM >> > Subject: Re: [GENERAL] pgsql data file location >> > >> > >> >> >> >> All that is possible. >> >> I you have not read the Admin Docs yet. >> >> >> >> Regds >> >> mallah. >> >> >> >> >> >> > I am trying to find where psql reads the data from when a table is >> > created. Also is it possible >> >> > to copy that data and transfer it onto another system running psql. > If >> > not does anyone know a >> >> > good psql backup solution. >> >> > >> >> > Thanks >> >> > >> >> > ismaila Kane >> >> >> >> >> >> >> >> ----------------------------------------- >> >> Get your free web based email at trade-india.com. >> >> "India's Leading B2B eMarketplace.!" >> >> http://www.trade-india.com/ >> >> >> >> ----------------------------------------- >> Get your free web based email at trade-india.com. >> "India's Leading B2B eMarketplace.!" >> http://www.trade-india.com/ ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
-----Original Message-----
From: mallah@trade-india.com [mailto:mallah@trade-india.com]
Sent: Fri 4/11/2003 3:53 PM
To: Ismaila Kane
Cc: mallah@trade-india.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pgsql data file location> Hello Mallah,
>
> Tried the PG_dump command and it works great. Any idea about the location of the backup
> file?
pg_dump is supposed to outout to stdout. you can redirect to a file using >
$ pg_dump dbname > /path/to/dumpfile.sql
Also is the database backup or are the tables and schemas backed up too.
it backs up everything in the database except large objects(lo)
(ignore it if u dont know understand lo)
what else do u expect it to backup if not tables and schemas?
Any restore
> command to be used .
$ psql -f dumpfile.sql
>
> Thank you
>
> Ismaila Kane
> ----- Original Message -----
> From: <mallah@trade-india.com>
> To: <ikane@ciscolabs.net>
> Cc: <pgsql-general@postgresql.com>
> Sent: Friday, April 11, 2003 2:17 PM
> Subject: Re: [GENERAL] pgsql data file location
>
>
>>
>> Dear Kane,
>>
>> you are rite it does not say explicitly the location of data file .
>>
>> But if you are wanting to know it for backup purposes reasons you are in the wrong direction.
>>
>> The only way to make safe/consistent backups in postgresql is to use pg_dump or pg_dumpall.
>>
>> If you are just curious about the location, it is either passed to
> postmaster
>> by -D option or by environment PGDATA. for typical tar ball installs it is
>> /usr/local/pgsql/data/ in RPM based it is /var/lib/pgsql/data
>>
>> under data folder there exists a folder "base" , under "base" one folder
> per database.
>> these folders have names correspoding to internal ids in system catalogs. under these numeric
>> folder there are files for individual tables and
> indexes.
>>
>> but do not copy these files and folder thinking that you are "backing up" it will be futile ,
>> as said pg_dump/all is the only way of backing up.
>>
>> Regds
>> Mallah.
>>
>>
>>
>>
>>
>> > Yes I read the manual but does not say where the location of the data
> file is?
>> > ----- Original Message -----
>> > From: <mallah@trade-india.com>
>> > To: <ikane@ciscolabs.net>
>> > Cc: <pgsql-general@postgresql.org>
>> > Sent: Friday, April 11, 2003 4:14 AM
>> > Subject: Re: [GENERAL] pgsql data file location
>> >
>> >
>> >>
>> >> All that is possible.
>> >> I you have not read the Admin Docs yet.
>> >>
>> >> Regds
>> >> mallah.
>> >>
>> >>
>> >> > I am trying to find where psql reads the data from when a table is
>> > created. Also is it possible
>> >> > to copy that data and transfer it onto another system running psql.
> If
>> > not does anyone know a
>> >> > good psql backup solution.
>> >> >
>> >> > Thanks
>> >> >
>> >> > ismaila Kane
>> >>
>> >>
>> >>
>> >> -----------------------------------------
>> >> Get your free web based email at trade-india.com.
>> >> "India's Leading B2B eMarketplace.!"
>> >> http://www.trade-india.com/
>>
>>
>>
>> -----------------------------------------
>> Get your free web based email at trade-india.com.
>> "India's Leading B2B eMarketplace.!"
>> http://www.trade-india.com/
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/