How to solve pg_tblspc problem in postgress

This blog will talk about How to solve pg_tblspc problem in postgres

Sometime when we are doing some operation on Postgres database, we faced this error.

I faced  it,  when I  was  trying  to  get  values  from  database.

This issue throws error that could not open directory “pg_tblspc/<1234…x>”: No such file or directory

To  solve this  issue  you  first  have  to  create  dump  of  your  database. Steps are ahead …

• psql -h localhost -p 6666 -U postgres -d <yourDBname>-f “E:/xyz/abc.p”

(It will create dump file of your database)

• Then delete your old database after dumping it.

(dump database <yourDBname>)

• Then again restore it from your dump file…

Or, Its better to use your backup file while restoring..

If  problem  is  still  there then we need  to first restore that <abc.p/abc.sql> file  on  our  local  machine

• then  again  create  (.p)  file  as  dump  file
• And restore your postgres by using this new dump file…

You will not come across this problem again……..

Another way:-

1) Backup data with pg_dump

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f

“/usr/local/backup/10.70.0.61.backup” old_db

To list all of the available options of pg_dump, please issue following command.

• Ø pg_dump -?

-p, –port=PORT database server port number

-i, –ignore-version proceed even when server version mismatches

-h, –host=HOSTNAME database server host or socket directory

-U, –username=NAME connect as specified database user

-W, –password force password prompt (should happen automatically)

-d, –dbname=NAME connect to database name

-v, –verbose verbose mode

-F, –format=c|t|p output file format (custom, tar, plain text)

-c, –clean clean (drop) schema prior to create

-b, –blobs include large objects in dump

-v, –verbose verbose mode

-f, –file=FILENAME output file name

2) Restore data with pg_restore

pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v

“/usr/local/backup/10.70.0.61.backup”

To list all of the available options of pg_restore, please issue following command.

• Ø pg_restore -?

-p, –port=PORT database server port number

-i, –ignore-version proceed even when server version mismatches

-h, –host=HOSTNAME database server host or socket directory

-U, –username=NAME connect as specified database user

-W, –password force password prompt (should happen automatically)

-d, –dbname=NAME connect to database name

-v, –verbose verbose mode

Pushpraj Kumar,

Helical IT Solutions

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download