# Postgresql Upgrade using dumpall...botched



## daBee (Apr 24, 2018)

Hi folks.

Well, updated Postgresql from 9.6 to 10.3.1 after a `pg_dumpall`.  Now I can't get the new version started.  Not sure where to take it from here, as I've tried things and it won't initdb:


```
[Tue Apr 24 11:20:21 adminuser@serverbox /var/db/postgres] sudo service postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

 fixing permissions on existing directory /var/db/postgres/data10 ... initdb: could not change permissions of directory "/var/db/postgres/data10": Operation not permitted
[Tue Apr 24 11:20:26 adminuser@serverbox /var/db/postgres] ll
total 23
drwxr-xr-x   4 postgres  postgres    5 Apr 24 11:20 .
drwxr-xr-x  16 root      wheel      26 Apr 24 11:14 ..
-rw-------   1 postgres  postgres  731 Aug  9  2017 .psql_history
drwxr-xr-x   2 root      postgres    2 Apr 24 11:20 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96
[Tue Apr 24 11:20:58 adminuser@serverbox /var/db/postgres] sudo service postgresql start
pg_ctl: directory "/var/db/postgres/data10" is not a database cluster directory
```

Now, I CAN change the permissions on this, but it won't work anyway.  The user I am using as an admin is not `postgres`, obviously.

At this point, I'd like to obscure the username and password, and have access to the server admin run as `adminuser`.  Is this possible?  I can only think that this setup can be designed.  I can't get a role to start anyway.  The beginning of `Postgresql` has always been confusing.

Any insight appreciated. Cheers


----------



## SirDice (Apr 24, 2018)

`chown postgres /var/db/postgres/data10`


----------



## daBee (Apr 24, 2018)

SirDice said:


> `chown postgres /var/db/postgres/data10`



Yes I've tried that, and it didn't work.  I said that above.  


```
[Tue Apr 24 09:07:21 adminuser@serverbox /var/db/postgres] sudo chown postgres data10
[Tue Apr 24 09:07:42 adminuser@serverbox /var/db/postgres] ll
total 23
drwxr-xr-x   4 postgres  postgres    5 Apr 24 08:31 .
drwxr-xr-x  16 root      wheel      26 Apr 24 07:45 ..
-rw-------   1 postgres  postgres  731 Aug  9  2017 .psql_history
drwxr-xr-x   2 postgres  postgres    2 Apr 24 08:31 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96
[Tue Apr 24 09:07:43 adminuser@serverbox /var/db/postgres] sudo chmod 700 data10
[Tue Apr 24 09:07:59 adminuser@serverbox /var/db/postgres] ll
total 23
drwxr-xr-x   4 postgres  postgres    5 Apr 24 08:31 .
drwxr-xr-x  16 root      wheel      26 Apr 24 07:45 ..
-rw-------   1 postgres  postgres  731 Aug  9  2017 .psql_history
drwx------   2 postgres  postgres    2 Apr 24 08:31 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96
[Tue Apr 24 09:08:00 adminuser@serverbox /var/db/postgres] sudo /usr/local/etc/rc.d/postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/db/postgres/data10 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start

[Tue Apr 24 09:08:09 adminuser@serverbox /var/db/postgres] sudo /usr/local/etc/rc.d/postgresql start
2018-04-24 09:10:11.989 EDT [29981] LOG:  listening on IPv6 address "::1", port 5432
2018-04-24 09:10:11.989 EDT [29981] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-04-24 09:10:11.991 EDT [29981] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-04-24 09:10:11.996 EDT [29981] LOG:  ending log output to stderr
2018-04-24 09:10:11.996 EDT [29981] HINT:  Future log output will go to log destination "syslog".
[Tue Apr 24 09:10:12 adminuser@serverbox /var/db/postgres] c
[Tue Apr 24 09:10:36 adminuser@serverbox /var/db/postgres] pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: could not open PID file "/var/db/postgres/data10/postmaster.pid": Permission denied
[Tue Apr 24 09:13:26 adminuser@serverbox /var/db/postgres] sudo pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
```

So it's permissions issues on a `700`.


----------



## BSDAppentic3 (Apr 24, 2018)

daBee What you want to exactly? Give permissions to another user?


----------



## daBee (Apr 24, 2018)

I just want to get it going.  I'm upgrading from 9.6.  I did a `pg_dumpall`, and I can't even get this one to start up, even before any roles have been assigned.  The server isn't running, because the data folder has permissions issues.


----------



## SirDice (Apr 24, 2018)

As it's still new and there's nothing in the database yet, `rm -rf /var/db/postgres/data10` then run `service postgres initdb`. Once that's done `service postgres start`.


----------



## BSDAppentic3 (Apr 24, 2018)

daBee So you upgraded from 95 to 96?
Edit: You had give permissions to you actual account? Your user, it's in wheel, or it has full access but not like the root?
You're using this?: postgresql10-server-10.3_1     PostgreSQL is the most advanced open-source database available anywhere


----------



## daBee (Apr 24, 2018)

From 9.6 to 10, hence the need for an upgrade. 

`uid=1001(adminuser) gid=1001(adminuser) groups=1001(adminuser),0(wheel)`

When I use that user as owner, I go to 700, which is the same ownership of the `/data96` previous data directory.
Edit:  Know that I did a `pg_dumpall` and not the `pg_upgrade`


----------



## ShelLuser (Apr 24, 2018)

Why are you using pg_ctl to start your setup after you used the service command? That made little sense to me and probably also explains the error message for /var/db/postgres/data10 (at least to some degree).

This is also an unusual place for the pid file (default is in the actual data directory) so did you perhaps change any specific defaults, and if so how did you do that? Specifically: did you use specific parameters in /etc/rc.conf or did you rely on postgresql.conf?

Anyway, first I'd follow up on SirDice 's suggestion while also keeping your custom settings in mind.

Also don't rely on pg_ctl to start/stop the server, use `# service postgresql start/stop` instead.


----------



## daBee (Apr 24, 2018)

Probably because the service didn't show up as having started up.  I've not seen it started up on port 5432.  I changed no defaults.  I simply installed it and tried to get some users going, and I ran into the permissions issues.  At this time I can't find postgresql.conf anywhere outside the /data96 directory.  

Can I start again?  I can delete both directories as I have a dump already.


----------



## ShelLuser (Apr 24, 2018)

daBee said:


> At this time I can't find postgresql.conf anywhere outside the /data96 directory.
> 
> Can I start again?  I can delete both directories as I have a dump already.


Yah, follow SirDice's earlier suggestion. Delete the data10 directory, use the initdb parameter and then try to start it. That should do it.


----------



## BSDAppentic3 (Apr 24, 2018)

All right...
I've installed postgresql96-client-9.6.8. Then I installed postgresql10-server-10.3_1.
See the output: 
	
	



```
$ sudo /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
$ /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: could not open PID file "/var/db/postgres/data10/postmaster.pid": Permission denied
```
This means that I have no problems installing and upgrading it. The trouble was when I try to launch it. It didn't leave me launch it as non-root, neither as root, so hth I must launch it?
I think that I must add my actual account to launch the program.
I'm right?


----------



## BSDAppentic3 (Apr 24, 2018)

ShelLuser said:


> Why are you using pg_ctl to start your setup after you used the service command? That made little sense to me and probably also explains the error message for /var/db/postgres/data10 (at least to some degree).
> 
> This is also an unusual place for the pid file (default is in the actual data directory) so did you perhaps change any specific defaults, and if so how did you do that? Specifically: did you use specific parameters in /etc/rc.conf or did you rely on postgresql.conf?
> 
> ...



You MUST see this:

```
$ sudo service postgresql onestart
2018-04-24 17:52:15.166 -03 [16377] LOG:  listening on IPv6 address "::1", port 5432
2018-04-24 17:52:15.167 -03 [16377] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-04-24 17:52:15.199 -03 [16377] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-04-24 17:52:15.240 -03 [16377] LOG:  ending log output to stderr
2018-04-24 17:52:15.240 -03 [16377] HINT:  Future log output will go to log destination "syslog".
```
What does it means? I had been success running it?


----------



## ShelLuser (Apr 24, 2018)

BSDAppentic3 said:


> I think that I must add my actual account to launch the program.
> I'm right?


Please don't hijack someone's thread like this. All you're doing here is diverting the attention away from the actual problem as shared by daBee, this thread is about his problem first and foremost.

Your "problem" is non-existent, as you just displayed yourself, and it's not helping the OP.


----------



## BSDAppentic3 (Apr 24, 2018)

ShelLuser said:


> Please don't hijack someone's thread like this. All you're doing here is diverting the attention away from the actual problem as shared by daBee, this thread is about his problem first and foremost.
> 
> Your "problem" is non-existent, as you just displayed yourself, and it's not helping the OP.



If you see my last post, you'll see that I'm trying to launch the same program


----------



## BSDAppentic3 (Apr 24, 2018)

ShelLuser Anyway, I think that he must solved it now. But I'm reproducing it just for the case that someone that have the same/similar problem, have a clue to solve it.
I don't think that I'm ruining not "hijacking" something right now. Sorry if I did it before, but now I think I make you a question.


----------



## BSDAppentic3 (Apr 24, 2018)

ShelLuser In fact, since I installed the program and tried to launch the program with no success, I think that I have a problem. Not the same, because it is only a reproduction of such.


----------



## daBee (Apr 24, 2018)

ShelLuser said:


> Yah, follow SirDice's earlier suggestion. Delete the data10 directory, use the initdb parameter and then try to start it. That should do it.



Still the same problem:


```
[Tue Apr 24 04:11:58 adminuser@serverbox /var/db/postgres] sudo rm -R data10/
[Tue Apr 24 04:12:06 adminuser@serverbox /var/db/postgres] sudo /usr/local/etc/rc.d/postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data10 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start

[Tue Apr 24 04:13:08 adminuser@serverbox /var/db/postgres] ll
total 31
drwxr-xr-x   4 postgres  postgres    5 Apr 24 16:13 .
drwxr-xr-x  16 root      wheel      26 Apr 24 14:14 ..
-rw-------   1 postgres  postgres  731 Aug  9  2017 .psql_history
drwx------  19 postgres  postgres   24 Apr 24 16:13 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96
[Tue Apr 24 04:13:11 adminuser@serverbox /var/db/postgres] /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: could not open PID file "/var/db/postgres/data10/postmaster.pid": Permission denied
[Tue Apr 24 04:13:39 adminuser@serverbox /var/db/postgres]
```

Here are permissions on that directory:

`drwx------  19 postgres  postgres   24 Apr 24 16:13 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96`

So it's a 700, same user as my data96 directory.  Not my day at all.

Ah hold on a minute...still messin around here...


----------



## daBee (Apr 24, 2018)

OK, it says it's not in `/etc/rc.conf`, or is not executable.  So I'm going to do a fresh restart.  I might be back. Heh.


----------



## ShelLuser (Apr 24, 2018)

daBee said:


> Still the same problem:


Ok, the problem here is that your last step is wrong. Sort off.

I know that the system tells you to use pg_ctl to start the service but you should not do this. Instead always use service. So: `service postgresql start`.

Here's what I would do at this point:

cd /var/db/postgresql
sudo sh
rm -rf data10
service postgresql initdb
service postgresql start
exit
What's causing your error is that at the final step you're logged on as adminuser. But the directory /var/db/postgresql/data10 is owned by postgresql and its permissionmask is 700. By default the PID file for the process gets placed in the data directory, but your current account has no permission there. So it cannot create the pid file nor can it access the actual datafiles.

But the above list of commands will start the server.


----------



## daBee (Apr 24, 2018)

```
cd /usr/ports/databases/postgresql10-client
sudo make install clean
cd /usr/ports/databases/postgresql10-server
sudo make install clean

<reboot>

~] service postgres initdb
postgres does not exist in /etc/rc.d or the local startup
directories (/usr/local/etc/rc.d), or is not executable
```

It is indeed there:  `postgresql_enable="YES"`

No data directories at all.  That was a complete fresh install after a complete uninstall.  

Any ideas?


----------



## daBee (Apr 24, 2018)

ShelLuser said:


> Ok, the problem here is that your last step is wrong. Sort off.
> 
> Here's what I would do at this point:
> 
> ...



OK that got it working.  

`postgresql.conf` is in the data folder, protected.  Where should I move it?


----------



## ShelLuser (Apr 24, 2018)

daBee said:


> OK that got it working.
> 
> `postgresql.conf` is in the data folder, protected.  Where should I move it?


Leave it, that's where PostgreSQL expects the file to be as far as I know. At best you could make a symbolic link in /usr/local/etc for easier access, but that's as far as I'd take this.

Now that you got a working setup it's time to put psql to work and restore your data. Or...  you could consider copying data96 and then performing an upgrade. But restoring is probably easier.


----------



## daBee (Apr 25, 2018)

All done.  Worked well.


----------



## BSDAppentic3 (Apr 25, 2018)

ShelLuser
Hey, sorry if I get a bit angry or molest.
If you want to take a look of what I get when I ran 
	
	



```
service postgresql start
```
, well, here's mine:

```
$ sudo service postgresql onestart
2018-04-25 15:07:33.288 -03 [17352] LOG:  listening on IPv6 address "::1", port 5432
2018-04-25 15:07:33.288 -03 [17352] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-04-25 15:07:33.289 -03 [17352] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-04-25 15:07:33.333 -03 [17352] LOG:  ending log output to stderr
2018-04-25 15:07:33.333 -03 [17352] HINT:  Future log output will go to log destination "syslog".
```
The output means that I had been successful launching the command that you provided?
Thanks.


----------



## SirDice (Apr 25, 2018)

The output means PostgreSQL started successfully.


----------



## ShelLuser (Apr 25, 2018)

BSDAppentic3 said:


> The output means that I had been successful launching the command that you provided?


Yups, which is why I described your problem as non-existent in my previous message. If you tried to connect to the database server you'd see this for yourself.

Another way to check for this would be to use `service postgresql status` (or onestatus), `sockstat -4l` which would list the daemon listening on localhost and of course `pg_ctl status` but that only works when logged on as the UID which owns the actual database process.


----------

