Postgres Pro
Downloads
Home   >   mailing lists

Re: How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux? - Mailing list pgsql-general

From Raghavendra
Subject Re: How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?
Date
Msg-id CA+h6Ahhuovt0mmE0wMZkvFyRybX8ULbvr3ZWTvCrS9a5=8UwfA@mail.gmail.com
Whole thread Raw
In response to Re: How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?  (Siva Palanisamy <siva_p@hcl.com>)
List pgsql-general
Tree view
On Fri, May 4, 2012 at 3:31 PM, Siva Palanisamy <siva_p@hcl.com> wrote:

Hi Raghavendra,

 

Is it sure that we can copy only the data of Postgresql from one disk to other seamlessly and then I can reuse the content without any hassle? If so, tablespace is what I should create first?

Please clarify me on this.

 

Thanks & Regards,

Siva.


Yes, using tablespace, you can move object files across disks. Here is small example, I have done on my local box but you can tweak as per your tablespace locations.

First I place object in one tablespace and moved it another tablespace.

postgres=# create tablespace space1 location '/opt/PostgreSQL/9.1/tspace';
CREATE TABLESPACE

postgres=# create table foo(id int) tablespace space1;
CREATE TABLE

postgres=# \d foo
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Tablespace: "space1"

postgres=# insert into foo select generate_series(1,10000);
INSERT 0 10000
postgres=# \dt+ foo
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description
--------+------+-------+----------+--------+-------------
 public | foo  | table | postgres | 384 kB |
(1 row)

postgres=# select pg_tablespace_size('space1');
 pg_tablespace_size
--------------------
             397312
(1 row)

Now create a new one and move it.

postgres=# create tablespace new_space location '/opt/PostgreSQL/9.1/newspace';
CREATE TABLESPACE

postgres=# alter table foo set tablespace new_space;
ALTER TABLE

postgres=# \d foo
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Tablespace: "new_space"

postgres=# select pg_tablespace_size('new_space');
 pg_tablespace_size
--------------------
             397312
(1 row)

---
Regards,
Raghavendra
EnterpriseDB Corporation

pgsql-general by date:

Previous
From: Siva Palanisamy
Date:
Subject: Re: How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?
Next
From: Geo-x
Date:
Subject: Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)