DB Creations/ Drop DB :: Not working...

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

DB Creations/ Drop DB :: Not working...

soumik.bhattacharjee

Hi All,

 

Any idea how to solve this error, tried with pgadmin4 and also using psql CLI

 

And why this comes in?

 

postgres=# CREATE DATABASE symuatdb

postgres-#     WITH

postgres-#     OWNER = postgres

postgres-#     ENCODING = 'UTF8'

postgres-#     LC_COLLATE = 'en_US.utf8'

postgres-#     LC_CTYPE = 'en_US.utf8'

postgres-#     TABLESPACE = sym_m_data1

postgres-#     CONNECTION LIMIT = 10;

ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory

postgres=#

 

Thanks

Soumik

Reply | Threaded
Open this post in threaded view
|

Re: DB Creations/ Drop DB :: Not working...

Tom Lane-2
<[hidden email]> writes:
> postgres=# CREATE DATABASE symuatdb
> postgres-#     WITH
> postgres-#     OWNER = postgres
> postgres-#     ENCODING = 'UTF8'
> postgres-#     LC_COLLATE = 'en_US.utf8'
> postgres-#     LC_CTYPE = 'en_US.utf8'
> postgres-#     TABLESPACE = sym_m_data1
> postgres-#     CONNECTION LIMIT = 10;
> ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory

It looks like somebody's deleted the directory your tablespace sym_m_data1
is pointing at.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: DB Creations/ Drop DB :: Not working...

Ron-2
On 12/05/2018 11:29 AM, Tom Lane wrote:

> <[hidden email]> writes:
>> postgres=# CREATE DATABASE symuatdb
>> postgres-#     WITH
>> postgres-#     OWNER = postgres
>> postgres-#     ENCODING = 'UTF8'
>> postgres-#     LC_COLLATE = 'en_US.utf8'
>> postgres-#     LC_CTYPE = 'en_US.utf8'
>> postgres-#     TABLESPACE = sym_m_data1
>> postgres-#     CONNECTION LIMIT = 10;
>> ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory
> It looks like somebody's deleted the directory your tablespace sym_m_data1
> is pointing at.

$PGDATA/pg_tblspc?


--
Angular momentum makes the world go 'round.

Reply | Threaded
Open this post in threaded view
|

RE: DB Creations/ Drop DB :: Not working...

soumik.bhattacharjee
Thanks,

So I need to re-create - pg_tblspc : no such file or directory  ?

Or I need to re-install the complete postgres?



-----Original Message-----
From: Ron <[hidden email]>
Sent: Wednesday, December 05, 2018 6:49 PM
To: [hidden email]
Subject: Re: DB Creations/ Drop DB :: Not working...

On 12/05/2018 11:29 AM, Tom Lane wrote:

> <[hidden email]> writes:
>> postgres=# CREATE DATABASE symuatdb
>> postgres-#     WITH
>> postgres-#     OWNER = postgres
>> postgres-#     ENCODING = 'UTF8'
>> postgres-#     LC_COLLATE = 'en_US.utf8'
>> postgres-#     LC_CTYPE = 'en_US.utf8'
>> postgres-#     TABLESPACE = sym_m_data1
>> postgres-#     CONNECTION LIMIT = 10;
>> ERROR:  could not create directory
>> "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory
> It looks like somebody's deleted the directory your tablespace
> sym_m_data1 is pointing at.

$PGDATA/pg_tblspc?


--
Angular momentum makes the world go 'round.

Reply | Threaded
Open this post in threaded view
|

Re: DB Creations/ Drop DB :: Not working...

Shreeyansh dba
In reply to this post by soumik.bhattacharjee
Hi Soumik,

It seems your tablespace directory deleted by someone and you need to recreate tablespace for your database.

Go through the below link for more details.

https://www.postgresql.org/docs/10/manage-ag-tablespaces.html

Hope this will helps.



On Wed, Dec 5, 2018 at 10:40 PM <[hidden email]> wrote:

Hi All,

 

Any idea how to solve this error, tried with pgadmin4 and also using psql CLI

 

And why this comes in?

 

postgres=# CREATE DATABASE symuatdb

postgres-#     WITH

postgres-#     OWNER = postgres

postgres-#     ENCODING = 'UTF8'

postgres-#     LC_COLLATE = 'en_US.utf8'

postgres-#     LC_CTYPE = 'en_US.utf8'

postgres-#     TABLESPACE = sym_m_data1

postgres-#     CONNECTION LIMIT = 10;

ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory

postgres=#

 

Thanks

Soumik

Reply | Threaded
Open this post in threaded view
|

RE: DB Creations/ Drop DB :: Not working...

soumik.bhattacharjee

Thanks,

 

But i can see the below in server.

 

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ ls -ltr

total 0

lrwxrwxrwx. 1 postgres postgres 24 Dec  5 15:21 16386 -> /var/lib/postgresql/data

lrwxrwxrwx. 1 postgres postgres 28 Dec  5 15:27 16389 -> /var/lib/postgresql/data/idx

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ pwd

/var/lib/postgresql/data/pgdata/postgresql-db/pg_tblspc

 

 

Do I need to re-create the pg_tblspc or user defined tablespaces?

 

Thanks

Soumik

 

 

From: Shreeyansh Dba <[hidden email]>
Sent: Thursday, December 06, 2018 11:42 AM
To: Bhattacharjee, Soumik <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: Re: DB Creations/ Drop DB :: Not working...

 

Hi Soumik,

It seems your tablespace directory deleted by someone and you need to recreate tablespace for your database.

Go through the below link for more details.

https://www.postgresql.org/docs/10/manage-ag-tablespaces.html

Hope this will helps.

 

On Wed, Dec 5, 2018 at 10:40 PM <[hidden email]> wrote:

Hi All,

 

Any idea how to solve this error, tried with pgadmin4 and also using psql CLI

 

And why this comes in?

 

postgres=# CREATE DATABASE symuatdb

postgres-#     WITH

postgres-#     OWNER = postgres

postgres-#     ENCODING = 'UTF8'

postgres-#     LC_COLLATE = 'en_US.utf8'

postgres-#     LC_CTYPE = 'en_US.utf8'

postgres-#     TABLESPACE = sym_m_data1

postgres-#     CONNECTION LIMIT = 10;

ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory

postgres=#

 

Thanks

Soumik

Reply | Threaded
Open this post in threaded view
|

RE: DB Creations/ Drop DB :: Not working...

soumik.bhattacharjee
In reply to this post by Shreeyansh dba

postgres=# SELECT spcname FROM pg_tablespace;

   spcname

--------------

pg_default

pg_global

sym_m_data1

sym_m_index1

(4 rows)

 

postgres=#

 

From: Bhattacharjee, Soumik
Sent: Thursday, December 06, 2018 11:48 AM
To: 'Shreeyansh Dba' <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: RE: DB Creations/ Drop DB :: Not working...

 

Thanks,

 

But i can see the below in server.

 

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ ls -ltr

total 0

lrwxrwxrwx. 1 postgres postgres 24 Dec  5 15:21 16386 -> /var/lib/postgresql/data

lrwxrwxrwx. 1 postgres postgres 28 Dec  5 15:27 16389 -> /var/lib/postgresql/data/idx

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ pwd

/var/lib/postgresql/data/pgdata/postgresql-db/pg_tblspc

 

 

Do I need to re-create the pg_tblspc or user defined tablespaces?

 

Thanks

Soumik

 

 

From: Shreeyansh Dba <[hidden email]>
Sent: Thursday, December 06, 2018 11:42 AM
To: Bhattacharjee, Soumik <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: Re: DB Creations/ Drop DB :: Not working...

 

Hi Soumik,

It seems your tablespace directory deleted by someone and you need to recreate tablespace for your database.

Go through the below link for more details.

https://www.postgresql.org/docs/10/manage-ag-tablespaces.html

Hope this will helps.

 

On Wed, Dec 5, 2018 at 10:40 PM <[hidden email]> wrote:

Hi All,

 

Any idea how to solve this error, tried with pgadmin4 and also using psql CLI

 

And why this comes in?

 

postgres=# CREATE DATABASE symuatdb

postgres-#     WITH

postgres-#     OWNER = postgres

postgres-#     ENCODING = 'UTF8'

postgres-#     LC_COLLATE = 'en_US.utf8'

postgres-#     LC_CTYPE = 'en_US.utf8'

postgres-#     TABLESPACE = sym_m_data1

postgres-#     CONNECTION LIMIT = 10;

ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory

postgres=#

 

Thanks

Soumik

Reply | Threaded
Open this post in threaded view
|

Re: DB Creations/ Drop DB :: Not working...

Avinash Kumar
Hi Sounim,

Lets make it simple. 

Do the following steps.

$ psql
\db

You should see the list of tablespaces and their locations.

Check if the location specified for the tablespace => sym_m_data1 exists. If not, create it or fix the permission issues, if any. 

Regards,
Avinash Vallarapu. 




On Thu, Dec 6, 2018 at 4:19 PM <[hidden email]> wrote:

postgres=# SELECT spcname FROM pg_tablespace;

   spcname

--------------

pg_default

pg_global

sym_m_data1

sym_m_index1

(4 rows)

 

postgres=#

 

From: Bhattacharjee, Soumik
Sent: Thursday, December 06, 2018 11:48 AM
To: 'Shreeyansh Dba' <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: RE: DB Creations/ Drop DB :: Not working...

 

Thanks,

 

But i can see the below in server.

 

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ ls -ltr

total 0

lrwxrwxrwx. 1 postgres postgres 24 Dec  5 15:21 16386 -> /var/lib/postgresql/data

lrwxrwxrwx. 1 postgres postgres 28 Dec  5 15:27 16389 -> /var/lib/postgresql/data/idx

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ pwd

/var/lib/postgresql/data/pgdata/postgresql-db/pg_tblspc

 

 

Do I need to re-create the pg_tblspc or user defined tablespaces?

 

Thanks

Soumik

 

 

From: Shreeyansh Dba <[hidden email]>
Sent: Thursday, December 06, 2018 11:42 AM
To: Bhattacharjee, Soumik <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: Re: DB Creations/ Drop DB :: Not working...

 

Hi Soumik,

It seems your tablespace directory deleted by someone and you need to recreate tablespace for your database.

Go through the below link for more details.

https://www.postgresql.org/docs/10/manage-ag-tablespaces.html

Hope this will helps.

 

On Wed, Dec 5, 2018 at 10:40 PM <[hidden email]> wrote:

Hi All,

 

Any idea how to solve this error, tried with pgadmin4 and also using psql CLI

 

And why this comes in?

 

postgres=# CREATE DATABASE symuatdb

postgres-#     WITH

postgres-#     OWNER = postgres

postgres-#     ENCODING = 'UTF8'

postgres-#     LC_COLLATE = 'en_US.utf8'

postgres-#     LC_CTYPE = 'en_US.utf8'

postgres-#     TABLESPACE = sym_m_data1

postgres-#     CONNECTION LIMIT = 10;

ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory

postgres=#

 

Thanks

Soumik



--
9000799060
Reply | Threaded
Open this post in threaded view
|

Re: DB Creations/ Drop DB :: Not working...

Shreeyansh dba
In reply to this post by soumik.bhattacharjee
Hi Soumik,

It looks like your tablespace is as pointing correctly on 16386 to $PGDATA/pg_tblspc location. However,  files in this goes missing(16386).

May consider recreating the tablespace.



On Thu, Dec 6, 2018 at 4:19 PM <[hidden email]> wrote:

postgres=# SELECT spcname FROM pg_tablespace;

   spcname

--------------

pg_default

pg_global

sym_m_data1

sym_m_index1

(4 rows)

 

postgres=#

 

From: Bhattacharjee, Soumik
Sent: Thursday, December 06, 2018 11:48 AM
To: 'Shreeyansh Dba' <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: RE: DB Creations/ Drop DB :: Not working...

 

Thanks,

 

But i can see the below in server.

 

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ ls -ltr

total 0

lrwxrwxrwx. 1 postgres postgres 24 Dec  5 15:21 16386 -> /var/lib/postgresql/data

lrwxrwxrwx. 1 postgres postgres 28 Dec  5 15:27 16389 -> /var/lib/postgresql/data/idx

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ pwd

/var/lib/postgresql/data/pgdata/postgresql-db/pg_tblspc

 

 

Do I need to re-create the pg_tblspc or user defined tablespaces?

 

Thanks

Soumik

 

 

From: Shreeyansh Dba <[hidden email]>
Sent: Thursday, December 06, 2018 11:42 AM
To: Bhattacharjee, Soumik <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: Re: DB Creations/ Drop DB :: Not working...

 

Hi Soumik,

It seems your tablespace directory deleted by someone and you need to recreate tablespace for your database.

Go through the below link for more details.

https://www.postgresql.org/docs/10/manage-ag-tablespaces.html

Hope this will helps.

 

On Wed, Dec 5, 2018 at 10:40 PM <[hidden email]> wrote:

Hi All,

 

Any idea how to solve this error, tried with pgadmin4 and also using psql CLI

 

And why this comes in?

 

postgres=# CREATE DATABASE symuatdb

postgres-#     WITH

postgres-#     OWNER = postgres

postgres-#     ENCODING = 'UTF8'

postgres-#     LC_COLLATE = 'en_US.utf8'

postgres-#     LC_CTYPE = 'en_US.utf8'

postgres-#     TABLESPACE = sym_m_data1

postgres-#     CONNECTION LIMIT = 10;

ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory

postgres=#

 

Thanks

Soumik

Reply | Threaded
Open this post in threaded view
|

RE: DB Creations/ Drop DB :: Not working...

soumik.bhattacharjee

Thanks to you and All here.

 

I will follow the steps and update you all.

 

From: Shreeyansh Dba <[hidden email]>
Sent: Thursday, December 06, 2018 12:35 PM
To: Bhattacharjee, Soumik <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: Re: DB Creations/ Drop DB :: Not working...

 

Hi Soumik,

It looks like your tablespace is as pointing correctly on 16386 to $PGDATA/pg_tblspc location. However,  files in this goes missing(16386).


May consider recreating the tablespace.

 

 

On Thu, Dec 6, 2018 at 4:19 PM <[hidden email]> wrote:

postgres=# SELECT spcname FROM pg_tablespace;

   spcname

--------------

pg_default

pg_global

sym_m_data1

sym_m_index1

(4 rows)

 

postgres=#

 

From: Bhattacharjee, Soumik
Sent: Thursday, December 06, 2018 11:48 AM
To: 'Shreeyansh Dba' <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: RE: DB Creations/ Drop DB :: Not working...

 

Thanks,

 

But i can see the below in server.

 

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ ls -ltr

total 0

lrwxrwxrwx. 1 postgres postgres 24 Dec  5 15:21 16386 -> /var/lib/postgresql/data

lrwxrwxrwx. 1 postgres postgres 28 Dec  5 15:27 16389 -> /var/lib/postgresql/data/idx

postgres@postgresdb-uat-postgresql-5b9fb67fcd-dbpb4:~/data/pgdata/postgresql-db/pg_tblspc$ pwd

/var/lib/postgresql/data/pgdata/postgresql-db/pg_tblspc

 

 

Do I need to re-create the pg_tblspc or user defined tablespaces?

 

Thanks

Soumik

 

 

From: Shreeyansh Dba <[hidden email]>
Sent: Thursday, December 06, 2018 11:42 AM
To: Bhattacharjee, Soumik <[hidden email]>
Cc: pgsql-admin <[hidden email]>
Subject: Re: DB Creations/ Drop DB :: Not working...

 

Hi Soumik,

It seems your tablespace directory deleted by someone and you need to recreate tablespace for your database.

Go through the below link for more details.

https://www.postgresql.org/docs/10/manage-ag-tablespaces.html

Hope this will helps.

 

On Wed, Dec 5, 2018 at 10:40 PM <[hidden email]> wrote:

Hi All,

 

Any idea how to solve this error, tried with pgadmin4 and also using psql CLI

 

And why this comes in?

 

postgres=# CREATE DATABASE symuatdb

postgres-#     WITH

postgres-#     OWNER = postgres

postgres-#     ENCODING = 'UTF8'

postgres-#     LC_COLLATE = 'en_US.utf8'

postgres-#     LC_CTYPE = 'en_US.utf8'

postgres-#     TABLESPACE = sym_m_data1

postgres-#     CONNECTION LIMIT = 10;

ERROR:  could not create directory "pg_tblspc/16386/PG_10_201707211/24576": No such file or directory

postgres=#

 

Thanks

Soumik